Solved: Conditional formatting not working unless I step into a cell

[Lawrence]

I have the following code to change background and font colors in cell L328 based on the value of cell X328. Cell X328 value changes based on the selection in a combo box.

The problem is that nothing happens unless I step into any cell and get out. What am I missing?

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)

Select Case [X328].Value
Case Is = 1
[L328].Interior.ColorIndex = 1
[L328].Font.ColorIndex = 2
Case Is = 2
[L328].Interior.ColorIndex = 10
[L328].Font.ColorIndex = 2
Case Is = 3
[L328].Interior.ColorIndex = 6
[L328].Font.ColorIndex = 1
Case Is = 4
[L328].Interior.ColorIndex = 11
[L328].Font.ColorIndex = 2
Case Is = 5
[L328].Interior.ColorIndex = 8
[L328].Font.ColorIndex = 1
Case Is = 6
[L328].Interior.ColorIndex = 13
[L328].Font.ColorIndex = 2
Case Is = 7
[L328].Interior.ColorIndex = 22
[L328].Font.ColorIndex = 2
End Select
End Sub
[/vba]

[Ago]

i think that is because when you use VBA excel dont see it as a change.

couldnt you make this code a function or a modulcode and call it after the combobox selection?

[Lawrence]

I guess I have to find a better way of doing it, as the one I posted does some funky things.

Edit: Attached is a sample of what I am trying to do. I'd like for cell C39 to change formatting based on the value in cell P1 when a division is selected in the combo box.

[Bob Phillips]

.

[Lawrence]

Again, thank you Bob. You make it seem so effortless.

[Lawrence]

Quick questions relating to this exercise.

1) For some reason, when I do some functions like a cut paste of the data, it won't let me use the undo function. But, if I delete the code, everything is back to normal.

2) Based on Cell P1 value, I would like to Hide/Unhide a couple of rows. I added a Private Sub, but it is not firing based on the change. If I put the code in the appropriate line in the Worksheet_Calculate Sub, it does work but gives me an endless screen flicker. I did a search and came upon the screen updating function, but it still gave me a flicker.

It seems to me that I need something in the code to "get out" of it and return to normal operation. Can someone explain to me what I am missing to stop the flickering if I stick the hide code in the Worksheet_Calculate sub?

Also, if I keep the separate sub, I'd like to understand how you make sure it is firing. By that I mean, the Worsheet_Calculate Sub works everythime a Division is selected, so what's different about the Hide_Rows sub?

[Bob Phillips]

Quick questions relating to this exercise.

1) For some reason, when I do some functions like a cut paste of the data, it won't let me use the undo function. But, if I delete the code, everything is back to normal.

Running code destroys the undo stack. If you need it you have to create your own, not trivial.

2) Based on Cell P1 value, I would like to Hide/Unhide a couple of rows. I added a Private Sub, but it is not firing based on the change. If I put the code in the appropriate line in the Worksheet_Calculate Sub, it does work but gives me an endless screen flicker. I did a search and came upon the screen updating function, but it still gave me a flicker.

It seems to me that I need something in the code to "get out" of it and return to normal operation. Can someone explain to me what I am missing to stop the flickering if I stick the hide code in the Worksheet_Calculate sub?

This is caused by the code re-entering the Calculate event because you are changing things. You need to turn events off

Also, if I keep the separate sub, I'd like to understand how you make sure it is firing. By that I mean, the Worsheet_Calculate Sub works everythime a Division is selected, so what's different about the Hide_Rows sub?

You can't just invent events (well you can to an extent, but that is another story). Worksheet_calculate is an event that Excel monitors and will pass to VBA if your application is listening for that event. There is no Hide_rows event unfortunately, you need to add it to the calculate event, like so

[vba]

Private Sub Worksheet_Calculate()

Application.EnableEvents = False
Application.ScreenUpdating = False

Select Case Me.Range("P1").Value
Case Is = 1
Me.Range("C39").Interior.ColorIndex = 1
Me.Range("C39").Font.ColorIndex = 2
Case Is = 2
Me.Range("C39").Interior.ColorIndex = 10
Me.Range("C39").Font.ColorIndex = 2
Case Is = 3
Me.Range("C39").Interior.ColorIndex = 6
Me.Range("C39").Font.ColorIndex = 1
Case Is = 4
Me.Range("C39").Interior.ColorIndex = 11
Me.Range("C39").Font.ColorIndex = 2
Case Is = 5
Me.Range("C39").Interior.ColorIndex = 8
Me.Range("C39").Font.ColorIndex = 1
Case Is = 6
Me.Range("C39").Interior.ColorIndex = 13
Me.Range("C39").Font.ColorIndex = 2
Case Is = 7
Me.Range("C39").Interior.ColorIndex = 22
Me.Range("C39").Font.ColorIndex = 2
End Select

Me.Rows("42").EntireRow.Hidden = Me.Range("P1").Value < "4"
Me.Rows("48").EntireRow.Hidden = Me.Range("P1").Value < "4"

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
[/vba]

[Lawrence]

Running code destroys the undo stack. If you need it you have to create your own, not trivial.

Ah, I understand now. Every time I perform an action, the sub Worksheet_Calculate is triggered, therefore destroying the Undo stack. No biggie, I can add a CommandButton to switch to Manual/Auto calc to have access to the Undo stack in case I mess up something while moving data around. Something like this (a modified version of one of your posts):

[vba]
Private Sub CommandButton1_Click()

If CommandButton1.Caption = "Set to Auto Calc" Then
Application.Calculation = xlCalculationAutomatic
CommandButton1.Caption = "Set to Manual Calc"
Else
Application.Calculation = xlCalculationManual
CommandButton1.Caption = "Set to Auto Calc"
End If

Range("G2").Select

End Sub
[/vba]

This is caused by the code re-entering the Calculate event because you are changing things. You need to turn events off.

That?s where I got lost in my search, I found stuff about the ScreenUpdating application, but I didn?t realize I needed the EnableEvents application as well.

You can't just invent events (well you can to an extent, but that is another story). Worksheet_calculate is an event that Excel monitors and will pass to VBA if your application is listening for that event. There is no Hide_rows event unfortunately, you need to add it to the calculate event, like so.

Well, I tried. I get you now, so any other actions I would like to perform have to be in the Worsheet_Calculate sub.

BTW, thank you for the much simpler hiding a row code, much better than my long If/Then statement.

[Bob Phillips]

I have some buttons on a custom commandbar that I have one for calculation mode.

[vba]

Declare Function GetKeyState Lib "user32" (ByVal fnKey As Long) As Integer

Const vkShift As Integer = &H10

Private Sub SetCalculateMode() 'Shifted is automatic, unshifted is manual
Dim sMode As String
Dim nState As Long

If GetKeyState(vkShift) < 0 Then
Application.Calculation = xlManual
sMode = "Manual"
nState = msoButtonDown
Else
Application.Calculation = xlAutomatic
sMode = "Automatic"
nState = msoButtonUp
End If
With Application.CommandBars.ActionControl
.TooltipText = "Calculation mode is " & sMode
.State = nState
End With

End Sub
[/vba]

Clicking the button sets it to automatic, shift-click sets it to manual.

[Lawrence]

That would be nice but it is a bit too advanced for me.... I am thinking the code above for now with a button on every sheet to set the calc, unless there is a way to have the Worsheet_Calc sub fire ONLY when cell P1 changes.

[Bob Phillips]

No, calculate fires for any cell that forces an Excel calculation. You could remove the dropdown, and have a data validation cell in P1 itself, and then run the change event specifically on that cell, or even directly in C1.

[Lawrence]

No, calculate fires for any cell that forces an Excel calculation. You could remove the dropdown, and have a data validation cell in P1 itself, and then run the change event specifically on that cell, or even directly in C1.

Very nice, thank you so much. I don't know why I tried to make it harder on myself. I put the following code and it now works like a charm, yet it still gives me the opportunity to use the Undo stack in case I mess up (of course before using the validation).

[vba]Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then

Application.EnableEvents = False
Application.ScreenUpdating = False

Select Case Me.Range("P1").Value
Case Is = 1
Me.Range("C39").Interior.ColorIndex = 1
Me.Range("C39").Font.ColorIndex = 2
Case Is = 2
Me.Range("C39").Interior.ColorIndex = 10
Me.Range("C39").Font.ColorIndex = 2
Case Is = 3
Me.Range("C39").Interior.ColorIndex = 6
Me.Range("C39").Font.ColorIndex = 1
Case Is = 4
Me.Range("C39").Interior.ColorIndex = 11
Me.Range("C39").Font.ColorIndex = 2
Case Is = 5
Me.Range("C39").Interior.ColorIndex = 8
Me.Range("C39").Font.ColorIndex = 1
Case Is = 6
Me.Range("C39").Interior.ColorIndex = 13
Me.Range("C39").Font.ColorIndex = 2
Case Is = 7
Me.Range("C39").Interior.ColorIndex = 22
Me.Range("C39").Font.ColorIndex = 2
End Select

Me.Rows("42").EntireRow.Hidden = Me.Range("P1").Value >= "4"
Me.Rows("48").EntireRow.Hidden = Me.Range("P1").Value >= "4"

Application.ScreenUpdating = True
Application.EnableEvents = True
End If
End Sub
[/vba]


I also noticed that the above code works even if I remove both the Application.EnableEvents and Application.ScreenUpdating. Are they needed, and if so (or not) what is the reason?

BTW, I noticed your very cool formula in P1: =IF(C3<>"",--(SUBSTITUTE(C3,"Division ","")),"") Can you walk me through the functionality? I might be using it in several places and the names in the validation cell may not always be the same. Instead of Division 1, Division 2, etc... it might be California, Texas, New York, etc...

[Bob Phillips]

That is what I meant to do, but I got distracted and didn't finish it.

The EnableEvents and ScreenUpdating is not needed now, because you change nothing else, and we aren't listening for the Calculate event any more. But it is good practice to keep them in as a general strategy.

Te formula just extracts the number from the DV cell. I use SUBSTITUTE to remove 'Division ' (BTW, some of your values were mis-spelt), but this leaves text numbers so I use the double unary -- to make them numbers so that the code tests for 1 and not "1" etc, an aesthetic thing.

[Lawrence]

Te formula just extracts the number from the DV cell. I use SUBSTITUTE to remove 'Division ' (BTW, some of your values were mis-spelt), but this leaves text numbers so I use the double unary -- to make them numbers so that the code tests for 1 and not "1" etc, an aesthetic thing.

Yikes, I am lost, but no sweat there's always the search function or Google. Thanks for your help.

[Bob Phillips]

It is pretty straightforward if you break it down.

First the code is testing P1 for 1, 2,3 etc, so we have to get those values into P1.

But, C3 has a lookup list of Division 1, Division2, etc., so when we slect something we get that exact value.

Our formula therefore needs to convert the string ' Division 1' to the number 1. This part

SUBSTITUTE(C3,"Division ","")

does most of the work, it converst the string 'Division 1' to the string '1' by replacing 'Division ' with nothing.

The --(...) just changes a string '1' to a number 1. One - change '1' to -1, the other c hange it back to positive 1.

That's all there is really, The If just ensures that something has been selected in C3.