conditional formating.

[wilg]

Hi there. Ive been trying to research vba conditional formating for offsetting cells but having a difficult time finding exactly what I need.

I have a spreadsheet that is a simple schedule. I've run out of normal conditional formatting based on cell value in excel 2003 (Only 3 available) and need more conditions..

If in any other row is cell value is as example B6 = "Duty" as example I need B6,B5,C6,C5 surrounding cells to change to blue as example.

This would be the case for the any cell over the spreadsheet...any help is very much appreciated.

[CatDaddy]

post a sample workbook of what you want, i am a little unclear on your request but it is easily doable im sure

[wilg]

down and dirty example..

[CatDaddy]

[VBA]Sub alex()
Dim col As Variant
Dim r, lr As Long
ActiveWorkbook.Sheets(1).Activate
col = Array("B", "D", "F", "H", "J", "L", "N")
lr = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
For i = LBound(col) To UBound(col)
For r = 3 To lr Step 2

If Cells(r, col(i)).Text = "duty" Then
Range(Cells(r, col(i)).Offset(-1, 0), Cells(r, col(i)).Offset(0, 1)).Interior.Color = 15773696
End If
Next r
Next i
End Sub[/VBA]

[wilg]

....works awesome, except when I delete "duty" it stays the colour, I need it to go back to white.

[Bob Phillips]

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

With Target

If .Value = "duty" Then

.Offset(-1, 0).Resize(2, 2).Interior.Color = 15773696
ElseIf .Value = "" Then

.Offset(-1, 0).Resize(2, 2).Interior.ColorIndex = xlColorIndexNone
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub[/VBA]

[wilg]

xld yours works to what I need to make the cells back to no color. Where do I find the color index for other colors? Do you have a good link. Thanks again.

[Bob Phillips]

[VBA]Sub ColorindexList()
Dim i As Long

Application.ScreenUpdating = False

With ActiveWorkbook

.Worksheets.Add After:=.Worksheets(.Worksheets.Count)
With ActiveSheet

.Name = "ColorindexList"
.Range("A1:B1").Value = Array("Colour", "Colorindex")
For i = 1 To 56

.Range("A" & i + 1).Interior.ColorIndex = i
.Range("B" & i + 1).Value = i
Next i

.Columns("B").AutoFit
End With
End With

Application.ScreenUpdating = True
End Sub[/VBA]