Count Conditionally Formatted Colored Cells in Named Range

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
Good day I have tried numerous examples but not working with my data set.

I have a named Range "Days_Overdue_Format". This range is made up of different sections in sheet and does not always follow each other e.g. =Questions!$O$6:$O$68,Questions!$O$73:$O$92 etc. (too long to post here)

I want to count the colored cells in Range and then return this value to a cell. e.g. there are for instance 25 red, 42 orange cells in range. The cells are colored using conditional formatting.

So result should look something like: (must also update if cell changes color)

Green (Colored using CF - RGB(0,255,0)9
Orange (Colored using CF - RGB(255,192,0)42
Yellow (Colored using CF - RGB(255,255,0)18
Red (Colored using CF - RGB(255,0,0)25

Thank you.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
You should be able to using the COUNTIF or COUNTIFS function, using the same criteria that you used in Conditional Formatting in these formulas to get your counts.
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
Hi Joe

Will try that... but will it work if the result of CF value is different for a red or orange range... maybe could give me an example to work from. More or less know a bit more about VBA than actual formulas... thanks... will first try to see if can get the formula to work
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
Will try that... but will it work if the result of CF value is different for a red or orange range... maybe could give me an example to work from. More or less know a bit more about VBA than actual formulas... thanks... will first try to see if can get the formula to work
You have a different CF rule for each color, right?
Similarly, you would have a different COUNTIF formula for each color.

If you have any trouble figuring it out, please post one of your Conditional Formatting formulas, along with the range it is applied to.
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123

ADVERTISEMENT

Thanks... will do when back at work tomorrow...
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123

ADVERTISEMENT

Hi Joe...

I have tried with CountIf but seem to be doing something wrong as no values are returned.

So conditional formatting is as follows:

Rule (applied in order shown)
Format
Applies to
Cell contains a blank value
Light Green (RGB(204,255,204))
Days_Overdue_Format (Named Range)
Cell Value contains "Day/s Overdue"
Red (Colored using CF - RGB(255,0,0)
Days_Overdue_Format (Named Range)
Formula:=ABS(TODAY()-L6:L25)>=30
Green (Colored using CF - RGB(0,255,0)
Days_Overdue_Format (Named Range)
Formula:=ABS(TODAY()-L6:L25)<=30
Yellow (Colored using CF - RGB(255,255,0)
Days_Overdue_Format (Named Range)
Formula:=ABS(TODAY()-L6:L25)<=30
Orange (Colored using CF - RGB(255,192,0)
Days_Overdue_Format (Named Range)

Days_Overdue_Format = =Questions!$O$6:$O$68,Questions!$O$73:$O$92,Questions!$O$97:$O$113,Questions!$O$118:$O$123,Questions!$O$128:$O$138,Questions!$O$143:$O$170,Questions!$O$175:$O$197,Questions!$O$202:$O$214,Questions!$O$219:$O$226,Questions!$O$231:$O$248,Questions!$O$253:$O$269,Questions!$O$274:$O$286,Questions!$O$291:$O$336,Questions!$O$341:$O$355,Questions!$O$360:$O$400,Questions!$O$405:$O$434,Questions!$O$439:$O$521,Questions!$O$526:$O$530,Questions!$O$535:$O$541,Questions!$O$546:$O$558,Questions!$O$563:$O$574,Questions!$O$579:$O$585,Questions!$O$590:$O$596

To give some more background.

User will manually enter a date in Column L (corresponding to Question in row), Column N has formula =Today().

Then in Column O there is a formula working out Days Overdue etc. That formula is =IF(ISBLANK(L6),"",IF(L6<N6,L6-N6&" Day/s Overdue",L6-N6&" Day/s until Overdue")).

So what I need (in image is the number of colored cells in Column R (as per image).

One thing I noticed now is the CF Formula is only for L6:L25. It is supposed to be the whole of Column L.

I do have a Count Colored Cells VBA which I use in another sheet which works perfectly, but does not in this one...

Thanks.
Screenshot 2021-04-21 094326.png
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
Update...

Updated CF Formula L6:L25 to L:L ... now it makes everything green and not as it was...:confused::oops::rolleyes:o_O
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
Did you see the link I posted in my previous post that says that COUNTIF will NOT work on non-contiguous cells?
They have some workarounds.

In your posting of your CF rules, I think you may have made a mistake, as the rules for both yellow and orange look exactly the same!

I don't think the manual "Count By Color" VBA code works on ranges colored by Conditional Formatting. I believe it only works on cells that are manually colored.

Also note that if you change the range of your Conditional Formatting, you will probably need to change your CF formula to reflect that change (the formula would need to reflect the first cell in the range, cell L1).
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
Hi
Did you see the link I posted in my previous post that says that COUNTIF will NOT work on non-contiguous cells?
They have some workarounds.

In your posting of your CF rules, I think you may have made a mistake, as the rules for both yellow and orange look exactly the same!

I don't think the manual "Count By Color" VBA code works on ranges colored by Conditional Formatting. I believe it only works on cells that are manually colored.

Also note that if you change the range of your Conditional Formatting, you will probably need to change your CF formula to reflect that change (the formula would need to reflect the first cell in the range, cell L1).
Hi Joe

Yes I did read through the example of COUNTIF not working on non-contiguous cells. I tried it and it did not work...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top