# Count Conditionally Formatted Colored Cells in Named Range

#### Jimmypop

##### Board Regular
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

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
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

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

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

#### Jimmypop

##### Board Regular

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.

#### Jimmypop

##### Board Regular
Update...

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

#### Joe4

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
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...

Replies
4
Views
99
Replies
6
Views
182
Replies
5
Views
103
Replies
3
Views
138
Replies
3
Views
125

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.

### Which adblocker are you using?

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

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