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

 

[dmoney]

this should do it

Sub formats()

Range("a1").Select
Do Until ActiveCell.Value = ""
Dim v1 As String
Dim v2 As String
Dim v3 As String
Dim v4 As String
Dim v As String
v1 = ActiveCell.Value
v2 = ActiveCell.Offset(1, 0).Value
v3 = ActiveCell.Offset(2, 0).Value
v4 = ActiveCell.Offset(3, 0).Value
v5 = v1 & v2 & v3 & v4
If v5 = "d/sd/sd/sd/s" Then
Range(ActiveCell.Address, ActiveCell.Offset(3, 0)).Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ElseIf v5 = "n/sn/sn/sn/s" Then
Range(ActiveCell.Address, ActiveCell.Offset(3, 0)).Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Else

End If
ActiveCell.Offset(1, 0).Activate
Loop

End Sub

 

[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

 

[dmoney]

send me an email and i will forward u the file i am using

 

[dmoney]

(e-mail address removed)

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)