VBA Code for Conditional Formatting Disappears

[Sgt Rock]

Am not sure how to ask this question, for am now at "wits end."
The following code does not set the cells to the specified settings:

With Worksheets("Threshold").Range("$F$27:$G$27")
                        .FormatConditions.Delete
                        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                        Formula1:="24999"
                        .FormatConditions(1).Interior.ColorIndex = 6
        End With
                        
                    
        With Worksheets("Threshold").Range("$H$27")
                        .FormatConditions.Delete
                        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                        Formula1:="9999"
                        .FormatConditions(1).Interior.ColorIndex = 6
        End With

However, if add a msgbox after the last End With, stop the code from running, view the cells, they are formatted correctly.
Am clueless as to what the problem is!
Thank for any suggestions!

 

[Joe4]

Where is this code placed?
How is it being called (a standard sub procedure, or an event procedure)?

Have you turned off ScreenUpdating and forgot to turn it back on?

Application.ScreenUpdating=True

 

[Sgt Rock]

Where is this code placed?
How is it being called (a standard sub procedure, or an event procedure)?

Have you turned off ScreenUpdating and forgot to turn it back on?

Application.ScreenUpdating=True

Thanks very much!
This is a bit complex - code is part of a worksheet change event. It is placed at the beginning of the module.
You may have something with the screen updating. The first part of code is shown below.

Are you having trouble typing in words here as I am? Thanks!!

Private Sub Worksheet_Change(ByVal Target As Range)

'This line of code clears the Activity column once a new chemical is selected from the select chemical drop down
'If Not...Is Nothing (doubles negative = a TRUE) will only work when C4 is changed
'If Intersect...Is Nothing means is not contained in the range
If Not Intersect(Target, Target.Worksheet.Range("C4")) Is Nothing Then
        With Worksheets("Threshold").ListObjects("ThresholdInputTable").ListColumns("ActivityTRIChemical")
            .DataBodyRange.ClearContents
            
        End With
        
'Default setting for threshold is 25,000 pounds for "manufacturing and processing" and 10,000 pounds for "otherwise using"
If Worksheets("Threshold").Range("C4").Value <> "Lead" Or Worksheets("Threshold").Range("C4").Value <> "Lead Compounds" Then
        With Worksheets("Threshold").Range("$F$27:$G$27")
                        .FormatConditions.Delete
                        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                        Formula1:="24999"
                        .FormatConditions(1).Interior.ColorIndex = 6
        End With
                        
                    
        With Worksheets("Threshold").Range("$H$27")
                        .FormatConditions.Delete
                        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                        Formula1:="9999"
                        .FormatConditions(1).Interior.ColorIndex = 6
        End With
        
        'MsgBox "got here"
        
End If

 

[Sgt Rock]

I just did a "got here 1" and "got here 2" MsgBox test - on the first loop through - the cells F27:H27 are formatted correctly,
F27:G27 if > 25,000 will be highlighted yellow, otherwise nothing

on the second loop through however, the formatting is changed for all the cells F27:H27 > 0!! So it has something to do with
the worksheet change event!!

 

[Joe4]

Note that if you are clearing cells, that triggers the Worksheet_Change event procedure to run again.
Typically, if you have any Worksheet_Change event procedure code that updates the entries in any cells, you need to disable the Events before making that change to keep it from triggering and calling itself (if you are not careful, you can catch yourself in a nasty endless loop!).


The way you typically do that is like this:

Application.EnableEvents = False
' Make the cell updates here
Application.EnableEvents = True

For more on this, see: http://2toria.com/2011/01/16/excel-vba-the-worksheet_change-event-and-application-enableevents/

 

[Sgt Rock]

You guessed correctly. If a new entry is placed in cell C4 - then a column of data is deleted in Table on same worksheet

[CODEPrivate Sub Worksheet_Change(ByVal Target As Range)
'This line of code clears the Activity column once a new chemical is selected from the select chemical drop down
'If Not...Is Nothing (doubles negative = a TRUE) will only work when C4 is changed
'If Intersect...Is Nothing means is not contained in the range
If Not Intersect(Target, Target.Worksheet.Range("C4")) Is Nothing Then
With Worksheets("Threshold").ListObjects("ThresholdInputTable").ListColumns("ActivityTRIChemical")
.DataBodyRange.ClearContents

End With][/CODE]

Tried the disable evens, did not work - maybe I placed in wrong place? Under the above code I this code with events disabled

If Worksheets("Threshold").Range("C4").Value <> "Lead" Or Worksheets("Threshold").Range("C4").Value <> "Lead Compounds" Then
Application.EnableEvents = False
        With Worksheets("Threshold").Range("$F$27:$G$27")
                        .FormatConditions.Delete
                        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                        Formula1:="24999"
                        .FormatConditions(1).Interior.ColorIndex = 6
        End With
                        
                    
        With Worksheets("Threshold").Range("$H$27")
                        .FormatConditions.Delete
                        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                        Formula1:="9999"
                        .FormatConditions(1).Interior.ColorIndex = 6
        End With
        
        'MsgBox "got here"
Application.EnableEvents = True
        
End If

Still does not format cells correctly?

 

[Joe4]

The bottom section is just formatting, not changing the cells values. I think you want it in this section:

If Not Intersect(Target, Target.Worksheet.Range("C4")) Is Nothing Then 
    Application.EnableEvents = False
    With Worksheets("Threshold").ListObjects("ThresholdInputTable").ListColumns("ActivityTRIChemical") 
        .DataBodyRange.ClearContents 
    End With
    Application.EnableEvents = True
...

 

[Sgt Rock]

Most courteous! Tried that - no go. Still shows a CF for all cells > 0.
Again, if I stop the code right after I CF the cells, they show correctly, i.e. F27:G27 > 24999 and H27 > 9999
I view the rules under "manage rules" under CF button
If I let the code run, then cells show only > 0.
Am stumped!!

 

[Joe4]

So we can try to recreate your situation exactly, please provide the following:
1. What the data on your worksheet looks like before this VBA code is invoked;
2. What you are doing/entering that invokes the VBA code (which exact cell reference and value you are entering);
3. What the expected result is.

Thanks