Excel VBA Conditional Formatting - Fixing a Range

[Corgano]

Hello all,

I'm a pure VBA beginner, working on some conditional formatting for eight conditions. The conditional formatting works fine but I'm recycling some basic internet found code and locking down the location in the spreadsheet I'm testing against is causing me problems.

Here's the code:

Private Sub Worksheet_Calculate()
Dim oCell As Range
For Each oCell In Range("BW6:EE189")
Select Case oCell.Value
Case Is = 0
oCell.Offset(0, -63).Interior.ColorIndex = xlNone
Case Is = -1
oCell.Offset(0, -63).Interior.ColorIndex = xlNone
Case Is > oCell.Offset(0, -67)
oCell.Offset(0, -63).Interior.ColorIndex = 1
Case Is > oCell.Offset(0, -70)
oCell.Offset(0, -63).Interior.ColorIndex = 3
Case Is > oCell.Offset(0, -71)
oCell.Offset(0, -63).Interior.ColorIndex = 45
Case Is > oCell.Offset(0, -72)
oCell.Offset(0, -63).Interior.ColorIndex = 36
Case Is > oCell.Offset(0, -73)
oCell.Offset(0, -63).Interior.ColorIndex = 35
Case Is > oCell.Offset(0, -74)
oCell.Offset(0, -63).Interior.ColorIndex = 4
End Select
Next oCell
End Sub

This was initially used to check a range of seven column vs. another range of seven columns so the offset worked great. In this case I'm checking 30 days in a month against five conditions located in rows A through E. As you can probably guess, day 1 works great but the offset is the incorrect function and days 2 onwards get funky refernece. In Excel itself I'd use an absolute location or the indirect function but neither function in VBA. I was reading through defining ranges but couldn't figure out how to incorporate that into this formula.

Any direction you guys can provide would be extremely helpful, I can usually puzzle things out but can't seem to get a good starting point.

Thanks!

 

[SkipVought]

Please post VBA code in Forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

You also need to explain wow the 5 columns of data relate to the 30 columns of data. Maybe post and example of the data and what you expect the result to be.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Corgano]

Thanks, will do!