• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Relative conditional formatting help

dronka

Member
Help! I'm creating a template for a calendar, and I want to conditionally format the number cells so that if there's a holiday (e.g. text) in the cell below it, it will turn red. So, for example, I'd like the "1" for New Year's Day to be red because there's text in the cell below it.


See the attached file (Red Text sheet). I've tried an istext conditional format in cells A3:G3, trying to get it to reference the cell below in row 4, but to no avail.


Here's the file:


https://dl.dropbox.com/u/55797447/ConditionalFormatingProblem.xlsx


Thanks!

David
 
@David... Welcome to chandoo.org and thanks for your question. Please take a minute to explore our forums to understand how we help you. You may also join our newsletter to get more.


http://chandoo.org/wp/subscribe/


Regarding your question, to set CF use below steps:


1. select A3:G3, goto CF > New rule

2. Type the rule as =A4<>""

3. Set the formatting.

4. Click ok.
 
Hi David ,


In case you plan to apply the rule to the entire month i.e. the range A3:G12 , then you need to modify the rule slightly to :


=AND(ISTEXT(A4),A4<>"")


so that the dates are not used to colour the cells above them.


Narayan
 
You both ROCK!! This works perfectly. I take it that leaving out the $ signs makes it all relative referencing. I was able to copy the formula for the first row into the other rows and it kept the reference for each cell as the cell below it.


Thank you, thank you, thank you!
 
Back
Top