Offset from unknown

[shippey121]

i have a workbook that require more than 3 conditional formats, im using the code below to overcome this

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

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

 

[Norie]

shippey

That syntax is wrong, but you probably know that already.

And it's probably Resize you want, not Offset.

Try this.

Private Sub Worksheet_Calculate()
Dim iCol As Long
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")
        Select Case c.Value
            Case m
                iCol = 44
            Case n
                iCol = 3
            Case o
                iCol = 4
            Case p
                iCol = 5
            Case q
                iCol = 6
            Case r
                iCol = 7
            Case s
                iCol = 8
            Case Else
                iCol = 0
        End Select
                
        c.Offset(, 5).Interior.ColorIndex = iCol
                    
    Next c

End Sub

 

[Smitty]

Try:

Range(c, c.Offset(,4)).Interior.ColorIndex = 44

HTH,

Smitty

 

[shippey121]

thanks for both your replies,

Norie i was looking at select case but as i said im a VBA beginner and couldnt figure it, however i used your code and it highlight only 1 cell, 5 cells to the right, what i required was to highlight all 5

Pennysaver, thanks your code worked just as i liked, the old saying So close yet so far, seems i was just a couple of speech marks away from gettin the right answer

anyway i think i will try to combine both

thanks again

 

[Norie]

Whoops.

I say you want to use Resize instead of Offset, so what do I do?

Use Offset in the code.

Try this.

c.Resize(, 5).Interior.ColorIndex = iCol

 

[shippey121]

thanks for your help i re-tried your code norie and just got loads of coloured cells, way more than i should have even the blank cells were getting coloured in anyway i managed to merge pennysavers code with yours to get

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("c8:r100")
        Select Case c.Value
            Case m
                Range(c, c.Offset(, 4)).Interior.ColorIndex = 44
            Case n
                Range(c, c.Offset(, 4)).Interior.ColorIndex = 39
            Case o
                Range(c, c.Offset(, 4)).Interior.ColorIndex = 4
            Case p
                Range(c, c.Offset(, 4)).Interior.ColorIndex = 5
            Case q
                Range(c, c.Offset(, 4)).Interior.ColorIndex = 6
            Case r
                Range(c, c.Offset(, 4)).Interior.ColorIndex = 7
            Case s
                Range(c, c.Offset(, 4)).Interior.ColorIndex = 8
            Case Else
                iCol = 0
        End Select
Next c

End Sub

this works fine and does exaclty what i need

 

[Norie]

Shippey

Another.

I didn't see that you were looking at other columns apart from column C.

 

[shippey121]

No problem, i used part of your code to simplyfy mine anyway, we all make mistakes, its the better people that can admit to them,