Conditional formatting - coloured text

[Jennie]

Hello,

I'm wondering if it's possible to change the font colour of a range of cells
depending on whether a different range of cells contain red text?

For example cells U1:IU1 contain dates which turn the text red when they
meet a ceratin condition.
I want the data in the cells below this row (U2:IU197) to also change to red
text if the top row is red.

So basically if U30 contains red text I want the whole of column U30 to turn
to red text. Can this be done? If so how?

Any help would be very much appreciated as I've been struggling for a
while!!

Thank you in advance

 

[Pete_UK]

You need to apply the same conditional formatting criteria that you
have in row 1 to the rows below. You may need to use Formula Is rather
than Cell Value Is in the first box of the DF dialogue. You can
highlight the range and enter the criteria once, rather than do it for
every cell individually.

If you are still uncertain, post back with details of your current CF
criteria.

Hope this helps.

Pete

 

[Jennie]

Thanks for the speedy response Pete!

I've tried this but it doesn't work. I think it might be becasue the top row
has dates in it and the other cells just have 1 digit figures.

The CF for the top row is

If cell value is greater than or equal to =$g$1 then
condition.....
G1 is TODAY-365

Perhaps my initial way of working out what I want is better.....

I basically need a formula to count the non blank cells within a rolling
year. - The dates
are in the top row, (U1:IU1) and the days/occassions sick are in the rows
below (U2:IU2, U3:IU3 and so on)

My initial formula: =SUMIF(($S$1:$IV$1>=TODAY()-365)*S2:IV2) adds up
all the figures (total days sick) correctly within the rolling 12 months,
but I also need to count the non blank cells (occasions) for the same
condition. i.e if someone has been off sick for 4 days in one week I want
excel to count this as 1 rather than 4.
I thought that changing 'SUMIF' to 'COUNTIF' would work, but it doesn't -
hense the diffrent strategy!

I've tried loads of different things but nothing seems to work. I'm well and
truely stuck!!!

Jennie

 

[Pete_UK]

Select all the cells in the range U2:IU197, with U2 as the first cell
that you select (it won't be highlighted like the others). Then click
on Format | Conditional Formatting and in the first box choose Formula
Is rather than Cell Value Is. In the formula box you should enter:

=U$1>=$G$1

then click the Format button and choose Red from the Colour box. Click
OK twice, and you should have what you want, as Excel will
automaticlly adjust that formula to suit all the cells in the range.

Hope this helps.

Pete

 

[Dave]

Hi,
Please post the criteria you used in the CF in cells U1:IU1
Regards - Dave.

 

[Jennie]

Hi Dave,

The CF in cells U1:IU1 is
Formula Is =U1>=TODAY()-365

Jennie

 

[Jennie]

I've tried this but for some reason excel isn't automatically adjusting the
formula to suit the other cells. It's keeping 'U1' throughout and therefore
isn't working. I've tried it without '$' but with no success - again it keeps
'U1' in the formula.

Do you have any other suggestions? I really appreciate your time and help.

Jennie

 

[Dave]

Hi,
Not sure if you'll have any more success than you're having with Pete's
suggestions, but here goes:
Select all cells in the range U2:IU197. Make sure you start the selection in
cell U2. When all cells are selected, U2 should be the only one that looks
different.
With all that selected, open the CF window, select formula is, and enter:
=U$1>=TODAY()-365
Select the CF's you want.
OK.

I've tested this here, so it should work...

Regards - Dave.

 

[Jennie]

Yippeee!! It's now working! Thank you so much. I'm not sure what was wrong
with it before as I had tried this previously. In the end I deleted all the
CF's and re-did them. That seemed to do the trick.

Would it be possible for me to now count and sum the cells containing red
text?

Thank you to you and Pete for your time and assistance. You've saved me a
lot of trial and error time!