Formula for CF

  • Thread starter Thread starter santaviga
  • Start date Start date
S

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
 
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
 
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
 
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
 
That has done the trick. many thanks Hutch

Mark

Tom Hutchins said:
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
Click to expand...
 
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
 
Back
Top