Conditional Formatting

[Chris Waller]

I have an Excel Spreadsheet that contains a formula in cell B2 that shows the
current month and year. In columns E4 to Q4 are headings that reflect each
month. Beneath each of the headings are 21 rows which contain monetary
amounts, however they can also contain zeros. I am trying to apply a
conditional format that will perform two functions. Firstly for the months
which have not yet been reached I want the column to contain a fill colour of
yellow and if the cell contains a zero I would like this text to be shown as
grey. If the month has been reached I would like there to be no fill colour
and I would still like the zero values to be shown as grey.

 

[Dav]

--------------------------------------------------------------------------------

I have an Excel Spreadsheet that contains a formula in cell B2 that
shows the
current month and year. In columns E4 to Q4 are headings that reflect
each
month. Beneath each of the headings are 21 rows which contain monetary
amounts, however they can also contain zeros. I am trying to apply a
conditional format that will perform two functions. Firstly for the
months
which have not yet been reached I want the column to contain a fill
colour of
yellow and if the cell contains a zero I would like this text to be
shown as
grey. If the month has been reached I would like there to be no fill
colour
and I would still like the zero values to be shown as grey.

1) Cell Value=0 then choose colour gray

2) it depends if you months are dates formated as months
choose formula and momth column eg E$4 >$b$4 then choose yellow

Regards

Dav

 

[Chris Waller]

FAO Dav

Thanks for your suggestion, however it does not appear to work for me. I
have checked the format of all the date fields and made them all the same.
Perhaps I did not explain myself very clearly on my initial posting. Ideally
what I would like to happen, is that all the zeros on the spreadsheet I want
to change to grey. All the columns that contain data after the current month
i.e. I want to colour yellow and all the columns containing data for the
current month and the previous months I want to show them with no fill. Hope
this helps clarify things.

 

[Dav]

what is the format of cell b2 and what does it display
eg July-06 format date or 0706 as text or 706 as number

If the dates in cells e4 to q4 are truly dates usually the first of the
month

If you are entering the conditionmal format to a cell in column E

The first condition should work

the second condition should have been e$4>$b$2

if b2 is not a date the condtion needs ammending accordingly eg if it
is 706
e$4>date(mod($b$2,100),$b$2/100,1),1)

or 0706 as text
e$4>date(right($b$2,2),left($b$2,2),1)


Sometimes excel puts " " around the formula in the conditional
formating if this is the case they need to be removed for the formula
to work

the formats need to be copied to the other cells to apply to them,
paste special_ formats

Regards

Dav

 

[Chris Waller]

FAO Dav

Sorry to trouble you again but only one of the conditional formats will work
at once. It appears that either the zero is greyed out or the column is
yellow, but I cannot get all the zeros grey in yellow column.

 

[Dav]

Which format have you put first, excel works out format 1 and if it is
not satisfied moves onto format2

so is the value conditional format=0 1st and the date conditional
format second?


Regards

Dav

 

[Chris Waller]

FAO Dav

I have swapped the formats around and neither way works.

If the first format is the formula and the 2nd is the cell value then the
zeros in the months that have passed are grey and the column has no fill
colour (This is correct). The months not yet reached are yellow but the zeros
are not grey (This is not correct).

If the cell value is 1st and the formula 2nd All zeros are grey but the
months not yet reached are filled with yellow, however the cells containing
zero values do not have a fill colour.

The problem could be that I am still working on version 98 of Word. Perhaps
this is a problem that has to be resolved using VBA?

 

[Dav]

It works fine on my computer but perhaps you could try

the date value first

then the formula as in cell e5

and(e5>0,condition you are currently using)

perhaps =and(e5>0,e$4>$b$2)

So it only changes the cells where the value is above 0 to yellow and
not the grey cells that it seems to overwrite

If that does not work i will think of something else! i am assuming
there are no negative values. We will get there!

Regards

Dav