Offset from unknown

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
i have a workbook that require more than 3 conditional formats, im using the code below to overcome this

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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
shippey

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

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

Try this.
Code:
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

Legend
Joined
May 15, 2003
Messages
29,536
Try:

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

HTH,

Smitty
 

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Whoops.:oops:

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

Use Offset in the code.:rolleyes:

Try this.
Code:
c.Resize(, 5).Interior.ColorIndex = iCol
 

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
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

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("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

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Shippey

Another. :oops:

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

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,113,861
Messages
5,544,721
Members
410,630
Latest member
JFORTH97
Top