shippey121
Well-known Member
- Joined
- Mar 21, 2006
- Messages
- 1,003
i have a workbook that require more than 3 conditional formats, im using the code below to overcome this
this highlights dates in my worksheet what im trying to do now is highlight the 4 cells to the right of the date, example if my date if found in c8 it will highlight, now i want D8:G8 to highlight as well
i tried using offset, now ive probably got it set out wrong but cant seem to get it to work
heres what i tried
but it doesnt work i get Method 'range' of object failed
Any Ideas on how to correct it or to alter my code to achive what i want, now im only a VBA begginer so if my code is rubbish go easy on me
TIA
Code:
Private Sub Worksheet_Calculate()
m = [c1].Value
n = [d1].Value
o = [e1].Value
p = [f1].Value
q = [g1].Value
r = [h1].Value
s = [i1].Value
Range("a8:r100").Interior.ColorIndex = 0
For Each c In Range("a8:r100")
If c.Value = m Then
c.Interior.ColorIndex = 44
End If
If c.Value = n Then
c.Interior.ColorIndex = 3
End If
If c.Value = o Then
c.Interior.ColorIndex = 4
End If
If c.Value = p Then
c.Interior.ColorIndex = 5
End If
If c.Value = q Then
c.Interior.ColorIndex = 6
End If
If c.Value = r Then
c.Interior.ColorIndex = 7
End If
If c.Value = s Then
c.Interior.ColorIndex = 8
End If
Next c
End Sub
this highlights dates in my worksheet what im trying to do now is highlight the 4 cells to the right of the date, example if my date if found in c8 it will highlight, now i want D8:G8 to highlight as well
i tried using offset, now ive probably got it set out wrong but cant seem to get it to work
heres what i tried
Code:
For Each c In Range("a8:r100")
If c.Value = m Then
Range("c:" & c.Offset(0, 3).Activate).Interior.ColorIndex = 44
End If
but it doesnt work i get Method 'range' of object failed
Any Ideas on how to correct it or to alter my code to achive what i want, now im only a VBA begginer so if my code is rubbish go easy on me
TIA