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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
Excel Formula:
=AND($A$2>=TODAY()+30,$A$2<>"")
You locked down the column and the row, so every single cell is locking at cell A2.
You only want to lock down the column reference, but let the row reference "float" so row 3 will lock at A3, row 4 will look at A4, etc.

So your formula should be:
Excel Formula:
=AND($A2>=TODAY()+30,$A2<>"")
and you will need to follow the same logic for all the other rules you created.

For a full explanation on these different type of cell references, see here: Absolute, Relative, and Mixed Cell References in Excel
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
You locked down the column and the row, so every single cell is locking at cell A2.
You only want to lock down the column reference, but let the row reference "float" so row 3 will lock at A3, row 4 will look at A4, etc.

So your formula should be:
Excel Formula:
=AND($A2>=TODAY()+30,$A2<>"")
and you will need to follow the same logic for all the other rules you created.

For a full explanation on these different type of cell references, see here: Absolute, Relative, and Mixed Cell References in Excel
:oops::eek:🙈🙈🙈 That one simple $ sign can mess me around like that. Thanks @Joe4... think this office getting to me sometimes... should have tried that.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
That one simple $ sign can mess me around like that. Thanks @Joe4... think this office getting to me sometimes... should have tried that.
You are welcome!

Yes, that "$" locks down the range reference that it precedes (column or row).
If you want to allow it float, you do not want it there.

The Conditional Formatting of a multi-celled range works the same way as if you copy a formula to multiple cells.
Anything with the "$" in front of it will be locked-down and not change as you copy down or across, while if you do not have it there, it can change.
So if you are unsure how the formula would react in Conditional Formatting, just put the formula in some empty cell on that row, and copy it down, and check out how it changes from row-to-row, to make sure it is changing appropriately.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,337
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