Microsoft VBA Function to count conditionally formatted coloured cells

Multi tool use
Multi tool use












0














Hi I have found a macro which successfully counts conditionally formatted coloured cells but when I try to change it into a function it doesn't work - I've read on here that it may be because .DisplayFormat.Interior.Color doesn't work for functions - does anybody know a workaround?



'Variable declaration
Dim lColorCounter2 As Long
Dim rngCell2 As Range
'loop throughout each cell in the range
For Each rngCell2 In Selection
'Checking Amber color
If Cells(rngCell2.Row, rngCell2.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
lColorCounter2 = lColorCounter2 + 1
End If
Next
MsgBox "Green =" & lColorCounter2


Ideally I would like the function to have two arguments the first the range of cells to search for the colours in and the second a cell with the colour I want to look for.










share|improve this question







New contributor




Dan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 2




    The best advice I can give you is to rethink the entire approach. You'd be better off with formulas that test the conditions used for the conditional formatting.
    – Rory
    Dec 27 '18 at 16:35






  • 2




    Take a look at: stackoverflow.com/questions/22366145/… . Chris' answer is pretty solid response to what can work, though the comments to the main post hold what i feel is the mos tvaluable info.
    – Cyril
    Dec 27 '18 at 16:41








  • 1




    Or this one: stackoverflow.com/questions/16330345/…
    – VBA Pete
    Dec 27 '18 at 16:42










  • Why are you writing it like Cells(rngCell2.Row, rngCell2.Column)? Just do rngCell2.DisplayFormat..., etc.
    – dwirony
    Dec 27 '18 at 17:04
















0














Hi I have found a macro which successfully counts conditionally formatted coloured cells but when I try to change it into a function it doesn't work - I've read on here that it may be because .DisplayFormat.Interior.Color doesn't work for functions - does anybody know a workaround?



'Variable declaration
Dim lColorCounter2 As Long
Dim rngCell2 As Range
'loop throughout each cell in the range
For Each rngCell2 In Selection
'Checking Amber color
If Cells(rngCell2.Row, rngCell2.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
lColorCounter2 = lColorCounter2 + 1
End If
Next
MsgBox "Green =" & lColorCounter2


Ideally I would like the function to have two arguments the first the range of cells to search for the colours in and the second a cell with the colour I want to look for.










share|improve this question







New contributor




Dan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 2




    The best advice I can give you is to rethink the entire approach. You'd be better off with formulas that test the conditions used for the conditional formatting.
    – Rory
    Dec 27 '18 at 16:35






  • 2




    Take a look at: stackoverflow.com/questions/22366145/… . Chris' answer is pretty solid response to what can work, though the comments to the main post hold what i feel is the mos tvaluable info.
    – Cyril
    Dec 27 '18 at 16:41








  • 1




    Or this one: stackoverflow.com/questions/16330345/…
    – VBA Pete
    Dec 27 '18 at 16:42










  • Why are you writing it like Cells(rngCell2.Row, rngCell2.Column)? Just do rngCell2.DisplayFormat..., etc.
    – dwirony
    Dec 27 '18 at 17:04














0












0








0







Hi I have found a macro which successfully counts conditionally formatted coloured cells but when I try to change it into a function it doesn't work - I've read on here that it may be because .DisplayFormat.Interior.Color doesn't work for functions - does anybody know a workaround?



'Variable declaration
Dim lColorCounter2 As Long
Dim rngCell2 As Range
'loop throughout each cell in the range
For Each rngCell2 In Selection
'Checking Amber color
If Cells(rngCell2.Row, rngCell2.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
lColorCounter2 = lColorCounter2 + 1
End If
Next
MsgBox "Green =" & lColorCounter2


Ideally I would like the function to have two arguments the first the range of cells to search for the colours in and the second a cell with the colour I want to look for.










share|improve this question







New contributor




Dan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











Hi I have found a macro which successfully counts conditionally formatted coloured cells but when I try to change it into a function it doesn't work - I've read on here that it may be because .DisplayFormat.Interior.Color doesn't work for functions - does anybody know a workaround?



'Variable declaration
Dim lColorCounter2 As Long
Dim rngCell2 As Range
'loop throughout each cell in the range
For Each rngCell2 In Selection
'Checking Amber color
If Cells(rngCell2.Row, rngCell2.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
lColorCounter2 = lColorCounter2 + 1
End If
Next
MsgBox "Green =" & lColorCounter2


Ideally I would like the function to have two arguments the first the range of cells to search for the colours in and the second a cell with the colour I want to look for.







excel vba excel-vba






share|improve this question







New contributor




Dan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question







New contributor




Dan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question






New contributor




Dan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked Dec 27 '18 at 16:33









Dan

1




1




New contributor




Dan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





Dan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






Dan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.








  • 2




    The best advice I can give you is to rethink the entire approach. You'd be better off with formulas that test the conditions used for the conditional formatting.
    – Rory
    Dec 27 '18 at 16:35






  • 2




    Take a look at: stackoverflow.com/questions/22366145/… . Chris' answer is pretty solid response to what can work, though the comments to the main post hold what i feel is the mos tvaluable info.
    – Cyril
    Dec 27 '18 at 16:41








  • 1




    Or this one: stackoverflow.com/questions/16330345/…
    – VBA Pete
    Dec 27 '18 at 16:42










  • Why are you writing it like Cells(rngCell2.Row, rngCell2.Column)? Just do rngCell2.DisplayFormat..., etc.
    – dwirony
    Dec 27 '18 at 17:04














  • 2




    The best advice I can give you is to rethink the entire approach. You'd be better off with formulas that test the conditions used for the conditional formatting.
    – Rory
    Dec 27 '18 at 16:35






  • 2




    Take a look at: stackoverflow.com/questions/22366145/… . Chris' answer is pretty solid response to what can work, though the comments to the main post hold what i feel is the mos tvaluable info.
    – Cyril
    Dec 27 '18 at 16:41








  • 1




    Or this one: stackoverflow.com/questions/16330345/…
    – VBA Pete
    Dec 27 '18 at 16:42










  • Why are you writing it like Cells(rngCell2.Row, rngCell2.Column)? Just do rngCell2.DisplayFormat..., etc.
    – dwirony
    Dec 27 '18 at 17:04








2




2




The best advice I can give you is to rethink the entire approach. You'd be better off with formulas that test the conditions used for the conditional formatting.
– Rory
Dec 27 '18 at 16:35




The best advice I can give you is to rethink the entire approach. You'd be better off with formulas that test the conditions used for the conditional formatting.
– Rory
Dec 27 '18 at 16:35




2




2




Take a look at: stackoverflow.com/questions/22366145/… . Chris' answer is pretty solid response to what can work, though the comments to the main post hold what i feel is the mos tvaluable info.
– Cyril
Dec 27 '18 at 16:41






Take a look at: stackoverflow.com/questions/22366145/… . Chris' answer is pretty solid response to what can work, though the comments to the main post hold what i feel is the mos tvaluable info.
– Cyril
Dec 27 '18 at 16:41






1




1




Or this one: stackoverflow.com/questions/16330345/…
– VBA Pete
Dec 27 '18 at 16:42




Or this one: stackoverflow.com/questions/16330345/…
– VBA Pete
Dec 27 '18 at 16:42












Why are you writing it like Cells(rngCell2.Row, rngCell2.Column)? Just do rngCell2.DisplayFormat..., etc.
– dwirony
Dec 27 '18 at 17:04




Why are you writing it like Cells(rngCell2.Row, rngCell2.Column)? Just do rngCell2.DisplayFormat..., etc.
– dwirony
Dec 27 '18 at 17:04












1 Answer
1






active

oldest

votes


















0














Have in mind that:




  1. RGB(255, 192, 0) is not green but close to orange.

  2. Change the range you want to loop - rng (now rng equals to Sheet1.Range("A1:A20"))


Try:



Option Explicit

Public Function Color(ByVal rng As Range)

Dim Counter As Long
Dim Cell As Range

For Each Cell In rng
'Checking Amber color
If Cells(Cell.Row, Cell.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
Counter = Counter + 1
End If
Next

MsgBox "Orange=" & Counter

End Function

Sub test()

Dim rng As Range

Set rng = Sheet1.Range("A1:A20")

Call Color(rng)

End Sub





share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });






    Dan is a new contributor. Be nice, and check out our Code of Conduct.










    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53948088%2fmicrosoft-vba-function-to-count-conditionally-formatted-coloured-cells%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    Have in mind that:




    1. RGB(255, 192, 0) is not green but close to orange.

    2. Change the range you want to loop - rng (now rng equals to Sheet1.Range("A1:A20"))


    Try:



    Option Explicit

    Public Function Color(ByVal rng As Range)

    Dim Counter As Long
    Dim Cell As Range

    For Each Cell In rng
    'Checking Amber color
    If Cells(Cell.Row, Cell.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
    Counter = Counter + 1
    End If
    Next

    MsgBox "Orange=" & Counter

    End Function

    Sub test()

    Dim rng As Range

    Set rng = Sheet1.Range("A1:A20")

    Call Color(rng)

    End Sub





    share|improve this answer


























      0














      Have in mind that:




      1. RGB(255, 192, 0) is not green but close to orange.

      2. Change the range you want to loop - rng (now rng equals to Sheet1.Range("A1:A20"))


      Try:



      Option Explicit

      Public Function Color(ByVal rng As Range)

      Dim Counter As Long
      Dim Cell As Range

      For Each Cell In rng
      'Checking Amber color
      If Cells(Cell.Row, Cell.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
      Counter = Counter + 1
      End If
      Next

      MsgBox "Orange=" & Counter

      End Function

      Sub test()

      Dim rng As Range

      Set rng = Sheet1.Range("A1:A20")

      Call Color(rng)

      End Sub





      share|improve this answer
























        0












        0








        0






        Have in mind that:




        1. RGB(255, 192, 0) is not green but close to orange.

        2. Change the range you want to loop - rng (now rng equals to Sheet1.Range("A1:A20"))


        Try:



        Option Explicit

        Public Function Color(ByVal rng As Range)

        Dim Counter As Long
        Dim Cell As Range

        For Each Cell In rng
        'Checking Amber color
        If Cells(Cell.Row, Cell.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
        Counter = Counter + 1
        End If
        Next

        MsgBox "Orange=" & Counter

        End Function

        Sub test()

        Dim rng As Range

        Set rng = Sheet1.Range("A1:A20")

        Call Color(rng)

        End Sub





        share|improve this answer












        Have in mind that:




        1. RGB(255, 192, 0) is not green but close to orange.

        2. Change the range you want to loop - rng (now rng equals to Sheet1.Range("A1:A20"))


        Try:



        Option Explicit

        Public Function Color(ByVal rng As Range)

        Dim Counter As Long
        Dim Cell As Range

        For Each Cell In rng
        'Checking Amber color
        If Cells(Cell.Row, Cell.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
        Counter = Counter + 1
        End If
        Next

        MsgBox "Orange=" & Counter

        End Function

        Sub test()

        Dim rng As Range

        Set rng = Sheet1.Range("A1:A20")

        Call Color(rng)

        End Sub






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 28 '18 at 8:36









        Error 1004

        1,23049




        1,23049






















            Dan is a new contributor. Be nice, and check out our Code of Conduct.










            draft saved

            draft discarded


















            Dan is a new contributor. Be nice, and check out our Code of Conduct.













            Dan is a new contributor. Be nice, and check out our Code of Conduct.












            Dan is a new contributor. Be nice, and check out our Code of Conduct.
















            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53948088%2fmicrosoft-vba-function-to-count-conditionally-formatted-coloured-cells%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            LR4Jjs1qMx 6KvnxgmdV,YIjSvQenQpat,h 2 r 6KoJH0oUxGT9m kQ2BIxF8aWAW8dUUMk ZXu2EiZ4j9ogqA1 KPa,keih
            0hj9,JY cypbcaB5u5G8LBx6Ah,Z64EriR4jrzCZnn1SbCcIueos9

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas