Raf
05-18-2009, 12:13 PM
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.
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.