Conditional Formatting/VBA

[Chris Waller]

FAO Dav,

Sorry, but none of the suggestions you put forward worked. I thought I would
re-post


I have a spreadsheet containing some information that I need to apply a
couple of conditional formats too, however this may have to be done using VBA
as I think it may be impossible to do this going through the normal
conditional format route.

In cell B2 there is a date (formatted to mmm) and in cells E4 to P4 I have
headed the columns with the months, April to March (again formatted to mmm)
i.e. a financial year. In cells E6 to Q26 is held all the financial
information for each month.

What I am trying to do, is change all the zero values to a gray font.
I also want to apply a yellow fill colour to ALL the values, i.e. zero
upwards, if they are in a column where the heading (E4 to P4) is greater than
the current month (B2). Where the column heading is less that the value in B2
(i.e. where the month has passed), I want no fill colour but I still want the
zero values to have a gray font.

 

[Sloth]

You can do this with conditional formatting and three conditions. Select
E6:Q6 and select Format->Conditional Formatting. Use "Formula Is" for all
three conditions, and use the following formulas.

=(E6=0)*(E$4>$B$2)
Format to grey font and yellow background

=(E6=0)*(E$4<=$B$2)
Format to grey font

=(E$4>$B$2)
Format to yellow background

 

[Chris Waller]

FAO Sloth,

That was brilliant!

Thanks again for your help, much appreciated.

Chris