Multiple Conditional Formatting

[cox ng \(1\)]

I have an annual report spreadsheet comprised of 15 rows (1 - 15) and 6
columns (A - F).



Column headings are "A = Name", "B = Date", "C = Wage", "D = Raise", "E = %
Recent", "F = % Total



In columns "A", "B" and "D" I manually add names or numbers as needed.
Columns "C" "E" and "F" are formulas based on values in "B" and "D". The
names in "A" remain constant, never requiring change. Rows are filled in
starting with dates in "B", with the most current raise in "D" reflecting
the date of the raise. For example, rows 1 through 9 might be filled in
with all past raises and dates; ending in row 9 with the most current raise
and date. Rows 10 through 15 would be presently blank. I highlight in
yellow the blank future cells in columns "B" and "D" by a conditional format
"formula is = ISBLANK(B9)". Current and past date rows have numbers but no
highlighted colors.



I wish to highlight in green the current year numbers in columns "B" and
"D", which will always be a cell above the first yellow blank cell, and a
cell below last years non-highlighted numbered cell. Each year as I add new
annual numbers to column "B" and "D", the previous green cells would change
from green to no highlight and the previous yellow blank cell would change
to a green highlight. This will always leave multiple non-highlighted
cells, multiple yellow highlighted cells, and one green highlighted cell.



What would condition #2 be in the conditional format formula to give me the
green cells while leaving the appropriate yellow cells and non-highlighted
cells?



Thank you for your time and help.



Regards,

Gary

 

[Pete_UK]

Probably something like:

=YEAR($B9)=YEAR(TODAY())

and format as green.You can use the format painter to apply this to
other cells.

Hope this helps.

Pete

 

[cox ng \(1\)]

This worked very well. Thank you for your time and expertise.

Regards,
Gary

 

[Pete_UK]

Glad it worked - thanks for feeding back.

Pete