VBA Code for Conditional Formatting Disappears

Sgt Rock

New Member
Joined
Aug 6, 2010
Messages
12
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:
Code:
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!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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?
Code:
Application.ScreenUpdating=True
 

Sgt Rock

New Member
Joined
Aug 6, 2010
Messages
12
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?
Code:
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!!

Code:
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

New Member
Joined
Aug 6, 2010
Messages
12
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
Code:
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

New Member
Joined
Aug 6, 2010
Messages
12
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

Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Code:
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

New Member
Joined
Aug 6, 2010
Messages
12
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,331
Messages
5,601,002
Members
414,419
Latest member
JRDunya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top