Microsoft VBA Function to count conditionally formatted coloured cells

Multi tool use
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
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.
add a comment |
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
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 likeCells(rngCell2.Row, rngCell2.Column)
? Just dorngCell2.DisplayFormat...
, etc.
– dwirony
Dec 27 '18 at 17:04
add a comment |
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
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
excel vba excel-vba
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.
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 likeCells(rngCell2.Row, rngCell2.Column)
? Just dorngCell2.DisplayFormat...
, etc.
– dwirony
Dec 27 '18 at 17:04
add a comment |
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 likeCells(rngCell2.Row, rngCell2.Column)
? Just dorngCell2.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
add a comment |
1 Answer
1
active
oldest
votes
Have in mind that:
- RGB(255, 192, 0) is not green but close to orange.
- 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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Have in mind that:
- RGB(255, 192, 0) is not green but close to orange.
- 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
add a comment |
Have in mind that:
- RGB(255, 192, 0) is not green but close to orange.
- 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
add a comment |
Have in mind that:
- RGB(255, 192, 0) is not green but close to orange.
- 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
Have in mind that:
- RGB(255, 192, 0) is not green but close to orange.
- 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
answered Dec 28 '18 at 8:36


Error 1004
1,23049
1,23049
add a comment |
add a comment |
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.
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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 dorngCell2.DisplayFormat...
, etc.– dwirony
Dec 27 '18 at 17:04