Multiple Conditional Formatting Problem

Miodrag J

New Member
Joined
Nov 28, 2017
Messages
5
Hello, everyone!

Thanks in advance for devoting your time to my problem.

I am stuck at a conditional formatting of a range of cells (do not have a problem when formatting one single cell but would like to avoid copy/pasting the formatting each time).

- I want to achieve the following:

Higlight each cell in a range if containing a specific text (let's say "YES") and if the date associated with the column which that cells belongs to has passed. Not sure if this was clear enough, so I will try to explain it in another way.

If a cell containing specific text (i.e. "YES") is in the column "C" and this column (actually cell "C1") is associated with a date that has passed, I want that cell highlighted with a specific color. Now, where I am stuck at is applying the conditional formatting to the whole range. It is probably a trivial question for many but I am still in the process of learning.

For single cells I am using the formula : =AND(C1<TODAY(), C2="YES"). So, what I want to do is, for example, to apply this conditional formatting to the range C2:E10. How do I arrange the custom formula so that it responds to the change of dates in columns C1:E1 and so that it responds to the above mentioned specific text in each particular cell in the range?

I hope I was not too confusing.

Best regards,
MJ
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
For single cells I am using the formula : =AND(C1< today(), c2="YES" ).

So what i want to do is for example apply this conditional formatting the range c2:e10. how arrange custom formula so that it responds change of dates in columns c1:e1 and above mentioned specific text each particular cell range?

I hope I was not too confusing.

Best regards,
MJ



Select cells C2:E10 and enter this CF formula
=AND(C$1< TODAY(), C2="YES")

Excel will automatically adjust the formula for each cell.

Forum TIP: The bulletin board editor sometimes interprets < and > symbols as HTML code. When you use them here in a formula, put a space before and after them.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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