Excel VBA Conditional Formatting - Fixing a Range 2

[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.

Skip from the other forum suggest I post here and provide how the 5 columns relate to the 30. Not sure how to post an example so here's a text try: The 30 columns represent 30 days in a month, they contain information on activity happening on each day taken from another source... say their widgit sales. What I'm trying to do is color each cell in the month based on the number of widgit sales as they reach a threshold, which is dynamic based on the sales group (what is in the rows) So if sales group one (row 6) hits 20%, 40%, 60% 80% or 100% of target (the five columns of data in A-E) color the cell in the month accordingly. The target is in column G, I just tried and failed to have VBA multiple an offset by a percentage so I tried to work around it. It was partially successful .

Please let me know if that's enough description.

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]

hi,

Give this a try...

Private Sub Worksheet_Calculate()
    Dim rMonths As Range        '1 row horozontal range where the dates are stored
    Dim rCriteria As Range      '1 row horizontal range where criteria headings are stored
    Dim rRow As Range           '1 column vertical range defining each row of data
    
    Set rMonths = Range("YourMonthRange")
'why do you even need a range of 5 columns,
'if the criteria is based on a FIXED percentage of ONE VALUE PER ROW???????
    Set rCriteria = Range("YourCriteriaRange")
'===========================================================================
    Set rRow = Range("YourRowRange")
    
    Dim r1 As Range, r2 As Range, r3 As Range
    
    For Each r1 In rRow
        For Each r2 In rMonths
            With Intersect(r1.EntireRow, r2.EntireColumn)
                Select Case .Value
                     Case r1.Value * 0.2
                         .Interior.ColorIndex = xlNone
                     Case r1.Value * 0.4
                         .Interior.ColorIndex = 2
                     Case r1.Value * 0.6
                         .Interior.ColorIndex = 1
                     Case r1.Value * 0.8
                         .Interior.ColorIndex = 3
                     Case r1.Value
                         .Interior.ColorIndex = 45
                End Select
            End With
        Next
    Next
End Sub

Skip,

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

 

[PHV]

As a starting point, replace this:
Case Is > oCell.Offset(0, -74)
with this:
Case Is > Cells(oCell.Row, "A")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Corgano]

Skip,

Thanks! To the five columns, as you seem to have guessed, they are unnecessary. Just me trying to work around the problem with my limited knowledge.

I have your solution mostly working (I think!). I left unclear one large item, the color codes need to be in ranges. I.e. be green if the value is from 20% to 40% (going for a heatmap view). I'm pretty sure this is identifying if the value is exactly the criteria * 0.2 rather than greater then the criteria * 0.2. Hopefully that makes sense. I'm going to mess with it a bit and see if I can get the syntax correct.

PHV,

That did something but I'm not experienced enough to bring it the rest of the way. Thank you for the advise though.

 

[SkipVought]

                     Case <= r1.Value * 0.2
                         .Interior.ColorIndex = xlNone
                     Case <= r1.Value * 0.4
                         .Interior.ColorIndex = 2
                     Case <= r1.Value * 0.6
                         .Interior.ColorIndex = 1
                     Case <= r1.Value * 0.8
                         .Interior.ColorIndex = 3
                     Case <= r1.Value
                         .Interior.ColorIndex = 45

Skip,

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

 

[Corgano]

Thank to both of you. I'm stubborn and got both solutions working! Not sure why PHV's gave me troubles are first but revisiting it worked smoothly.