Protecting Conditional Formatting

@MarkB
See https://excelribbon.tips.net/ExcelTipsMacros.html

I would love to use this tip, as I have created A TON of Conditional Formatting Rules for my worksheet that always seemed to get messed up. I don't have the time or the need to become an Excel expert (even though I read these tips everyday), but it seems like it would be better to post screen shots from Excel and VBA of where all this code is going, instead of just the code itself. I understand how to record a Macro, but one that "that applies the conditional formatting to the cells" is beyond me. Thanks for all the tips!

It looks like IsNot comes from VB.NET (aka .NET Visual Basic) and PowerShell. See https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/isnot-operator

What a dumb mistake! IsNot is no keyword in vba.

The correct syntax is:

If Not r is Nothing Then
If Not Application.Intersect(Target, r) Is Nothing Then

This proves that the macro in this tip has not been tested before publication!

@J.Woolley

Thanks I'll give it a go and see if I can get it to work. One thing I have noticed is that I get a compiling error at "IsNot"

(see Figure 1 below)

@Coenie
If you have VBA code for two Worksheet_Change events, separate them into two Sub procedures like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Proc1 Target
    Proc2 Target
End Sub

In the Visual Basic Editor (VBE), use Insert > Module to add a standard code module, then add the two Sub procedures to that module like this:

Sub Proc1(ByVal Target As Range)
    ... code from 1st Worksheet_Change event
End Sub

Sub Proc2(ByVal Target As Range)
    ... code from 2nd Worksheet_Change event
End Sub

Also, see https://sites.google.com/view/MyExcelToolbox/

Hi Allen,

I have tested your solution and it works for me. However, I'm not a vba expert but I have another worksheet change event already, how can I combine the two? Any help would be much appreciated

Kind Regards

Coenie

'Private Sub Worksheet_Change(ByVal Target As Range)
'
' Dim OldVal As String
' Dim NewVal As String
'
'
' ' If more than 1 cell is being changed
' If Target.Count > 1 Then Exit Sub
' If Not Intersect(Target, ActiveSheet.Range("A5:A5000,P5:P5000,V5:V5000")) Is Nothing Then
'
' Application.EnableEvents = False
'
' NewVal = Target.Value
'
' 'If there's nothing to undo this will cause an error
' On Error Resume Next
' Application.Undo
' On Error GoTo 0
'
' OldVal = Target.Value
'
' If OldVal = "" Then
'
' Target.Value = NewVal
' Else
' 'Delete cell contents
' If NewVal = "" Then
'
' Target.Value = ""
'
' Else
'
' 'This IF prevents the same value apprearing in the cell multiple times
' 'If you are happy to have the same value multiple timesremove this IF
' If InStr(Target.Value, NewVal) = 0 Then
'
' Target.Value = OldVal & "; " & NewVal
'
' End If
' End If
' End If
'
' Application.EnableEvents = True
'
' Else
'
' Exit Sub

You might also be interested in the conditional formatting backup and restore macros at My Excel Toolbox: https://sites.google.com/view/MyExcelToolbox/

For those who wonder, this will not work on an Excel spreadsheet hosted as an Office365 online document. The VBA code will be ignored.

Jerry,

From the article: "If you can record a macro that applies the conditional formatting to the cells, you could create some additional macros that apply that recorded macro, as needed. For instance, let's say that the macro you record is called something short and sexy like SetCondFormat."

Hi,

Checking, SetCondFormat doesn't seem to be an intrinsic function of Excel. I did find a thread on Stack Overflow at https://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code/13664575 that provided the following code implementing SetCondFormat.

Sub setCondFormat()
Range("B3").Select
With Range("B3:H63")
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($D3="""",FALSE,IF($F3>=$E3,TRUE,FALSE))"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
End With
End With
End Sub

It appears that the full solution would a user to identify and code for the formatting properties that are desired.