Excel Conditional Formatting- 3 Condition Limit

[jobrumo]

I know this has been discussed at length in other threads but I did not find the problem I am having.

I have used the code that allows direct entry of a value into a cell which causes the formatting to change, but it does not work when that cell's value is generated by a FORMULA.

Here's the code I use:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range("B19:AF25")) Is Nothing Then
Select Case Target
Case 1 To 5
icolor = 5
Case 6 To 10
icolor = 10
Case 11 To 15
icolor = 6
Case 16 To 20
icolor = 3
Case 21 To 25
icolor = 2
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

The formula is a nested "IF" statement that generates values between the ranges.

Thanks for any help.

 

[SkipVought]

You might have to fire using the WorksheetCalculate event. No Target range necessary.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[xlbo]

If the cell doesn't physically change then the change event won't fire..

As Skip says, you will probably need to use the worksheet CALCULATE event - that means applying the logic to all cells at once rather than just the one that has been changed however....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jobrumo]

1)If what you mean by worksheet calculate is the F9 key, I tried it and nothing changed.

2)The cell values do physically change (but not the color) when I enter a new value in another cell that is referenced in the formula of the cell that I am trying to change color.

3) Not sure what you mean by applying logic to all cells at once.

Any help would be appreciated. Thx.

 

[SkipVought]

"The cell values do physically change"

The FORMULA calculates a value that is DISPLAYED, and that DEEMS to change the cell.

The Cell Value is the FORMULA. THAT does not change!!!

Whenever some other value/formula changes that the formula references, the WorksheetCalculate event fires. If your workbook options are set to calculate automatically, you do not have to hit F9.

So, please post the code that you are running in the WorksheetCalculate event.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[xlbo]

1)If what you mean by worksheet calculate is the F9 key, I tried it and nothing changed.

No - there is an event, similar to the worksheet_CHANGE event that is fired when the sheet calculates. You need to use that event to do your conditional formatting

2)The cell values do physically change (but not the color) when I enter a new value in another cell that is referenced in the formula of the cell that I am trying to change color.

No, they don't. The calculated result changes but the formula within the cell remains the same so there is no physical change - that is why the change event will not work for this

3) Not sure what you mean by applying logic to all cells at once.

If you use the calculate event, there is no TARGET cell as the whole worksheet is recalced so you will need to write code that checks ALL your cells and apply the formatting to all of them rather than just one that has changed

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jobrumo]

I believe the code you are asking for is posted above on the original submittal.

The formulas in the cells (B19:AF25) are a nested IF statement that refers to another cell's inputted value. The displayed number in the cell is supposed to change its color, but since you set me straight on a cell's value vs. what is displayed, I understand why it doesn't work.

I could post the entire Excel workbook, if that would help. The file is a crude way of fomatting the correct quantity of cells based on a value placed elsewhere.

I have gotten it to work using the 3-rule conditional formatting, but I need 4 colors.

Any (more) help would be appreciated. Thx.

 

[SkipVought]

"I believe the code you are asking for is posted above on the original submittal."

THAT is exactly the problem. That code will NOT run, as Target is not a variable in the WorksheetCalculate event as BOTH Geoff and I clearly stated.

You must loop thru the entire range to evaluate...

Private Sub WorksheetCalculate()

Dim icolor As Integer, r as range

For each r in range(yourrange)
        Select Case r.value
            Case 1 To 5
                icolor = 5
            Case 6 To 10
                icolor = 10
            Case 11 To 15
                icolor = 6
            Case 16 To 20
                icolor = 3
            Case 21 To 25
                icolor = 2
            Case 26 To 30
                icolor = 42
            Case Else
                'Whatever
        End Select
        
        r.Interior.ColorIndex = icolor
   
next
End Sub

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[jobrumo]

OK, I got it to work, but can only get it to re-calculate when I am in the vba code and use F5. The people who will use this spreadsheet are even less Excel-savvy than I am. How can I get it to re-calculate from the Excel sheet itself?

Thanks.

 

[dgillz]

Do you have the sheet set to manual re-calculation?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[jobrumo]

It is set to automatic calculate. I tried F9 even though it is on automatic and nothing happens.