Formula for CF

[santaviga]

Dear All,

I have a range of cells. (a1 to az1) these cells will be input with D/S or
N/S for shifts, what I need is a CF format to highlight the cells and bold if
more than 4 D/S are in a row such as D/S D/S D/S D/S D/S in cells A1 A2 A3 A4
A5 these will highlight a colour and bold and the same for N/S also. can
anyone help me on this small problem.

many thanks.

MN

 

[Tom Hutchins]

One way, using a helper row...

In row 2 (insert a new row 2 if necessary), enter this formula in B2:
=IF(B1=A1,A2+1,1)
Copy B2 to the right through AZ2.

Select row 1. Select Format >> Conditional Formatting. Change 'Cell Value
Is' to 'Formula Is', and enter the formula
=(A2>4)
Click the Format button and select the formatting you want. Click OK and OK
to close the Conditional Formatting dialog. You can hide row 2 if desired.

Hope this helps,

Hutch

 

[Tom Hutchins]

Besides the formula in B2, you should enter the number 1 in cell A2.

Hutch

 

[santaviga]

Hi Tom, Tried this but returning false figures further along the line, I
thought of AND function in CF with some sort of formula in there. Any further
ideas??

MN

 

[Tom Hutchins]

Are the 'false figures' caused by blank cells? If so, try this formula in B2:
=IF(AND(B1=A1,LEN(B1)>0),A2+1,1)
Copy across through AZ1.

I don't see a way to do all this just using a conditional formatting formula
(with no helper row), but I will keep experimenting.

Hutch

 

[santaviga]

That has done the trick. many thanks Hutch

Mark

Are the 'false figures' caused by blank cells? If so, try this formula in B2:
=IF(AND(B1=A1,LEN(B1)>0),A2+1,1)
Copy across through AZ1.

I don't see a way to do all this just using a conditional formatting formula
(with no helper row), but I will keep experimenting.

Hutch

 

[santaviga]

Hi, Need a little more help on this one, input the macro but when 5 x D/S in
a row and macro run doesn't do anything, any suggestions.

regards


Mark

 

[santaviga]

just sent an email to you.

many thanks

(e-mail address removed)