Solved: Format code for multiple areas

[Raf]

Hi there,

I’ve created this code to format a sheet which will be used as an agenda.


Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range, r6 As Range, r7 As Range, r8 As Range, myMultiAreaRange As Range

Set r1 = Range("A6:a50")
Set r2 = Range("G6:G50")
Set r3 = Range("M6:M50")
Set r4 = Range("S6:S50")
Set r5 = Range("Y6:Y50")
Set r6 = Range("AE6:aE50")
Set r7 = Range("AK6:aK50")
Set r8 = Range("AQ6:aQ50")
Set myMultiAreaRange = Union(r1, r2, r3, r4, r5, r6, r7, r8)



For Each c In myMultiAreaRange
If c.Value < Range("B3") Or c.Value >= Range("C3") And c.Value < Range("B4") Or c.Value >= Range("C4") Then

c.Offset(0, 1).Range("A1:d1").Interior.ColorIndex = 5

Else
c.Offset(0, 1).Range("A1:d1").Interior.ColorIndex = 0

End If
Next

End Sub


How can I change this code so that if c is in another range (e.g. range G6:G50) of myMultiAreaRange, it will also look at cells that belong to the range above that area (H3 instead of B3 and I3 instead of C3 etc)?

So in this part:
If C.Value < Range("B3") Or c.Value >= Range("C3") And c.Value < Range("B4") Or c.Value >= Range("C4") Then
All ranges should somehow be variables depending on where c is found.

Please help me out!

Raf.

[Kenneth Hobs]

If c.Value < cells(3,c.column+1) Or c.Value >= cells(3,c.column+2) And c.Value < cells(4,c.column+1) Or c.Value >= cells(4,c.column+2) Then

[Raf]

Thanks Kenneth,

That one works! I'm very hapy!!!