A challenge

[Guest]

HI, I have a spread sheet where I key in a letter in each day against some
ones name I.E. H for Hol S for Sick, What I would like to do is when I key in
five S's, the fith S turns red. any one got any Ideas?.

Thanks in advance Wildauk

 

[Ken Puls]

I'd use a conditional format to do that.

Just to illustrate, assume that you are working in A1:E1 (I'll let you
port it to the exact range.)

Select Cell E1 and then go to the Format menu. Choose "Conditional
Formatting". In the box that pops up, change the Condition1 to be
"Formula Is" and enter the following formula:
=COUNTIF(D3:H3,"S")=5

Next, click the Format tab and change the font colour to Red. Click
Okay till you're out.

Enter an S in each cell from A1:E1. As soon as you enter the 5th, E1
should turn red.

Note that this is case sensitive, so you'll need to adjust the formula
if you want to evaluate lower case s's or a mixture. Personally, I'd
throw some data validation on the cells to force my users to enter only
choices that I wanted though.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[Guest]

Thanks Ken I'll Give it a go tomorrow. wildauk

I'd use a conditional format to do that.

Just to illustrate, assume that you are working in A1:E1 (I'll let you
port it to the exact range.)

Select Cell E1 and then go to the Format menu. Choose "Conditional
Formatting". In the box that pops up, change the Condition1 to be
"Formula Is" and enter the following formula:
=COUNTIF(D3:H3,"S")=5

Next, click the Format tab and change the font colour to Red. Click
Okay till you're out.

Enter an S in each cell from A1:E1. As soon as you enter the 5th, E1
should turn red.

Note that this is case sensitive, so you'll need to adjust the formula
if you want to evaluate lower case s's or a mixture. Personally, I'd
throw some data validation on the cells to force my users to enter only
choices that I wanted though.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca