prevent clear color by condition formatting

[Hasson]

hello

I use some colors by condition formatting when change or add new data but I don't want allow for anybody clear the color when enter condition formatting to clear the color by message box " you have not a permission do that" ,is it possible?

 

[severynm]

Try protecting the worksheet, making sure that "Format cells" is unchecked.

 

[Hasson]

thanks but this is not a good choice because it protects and disable other properties , I would just disable condition formatting

is it possible by code or any other thing?

 

[severynm]

AFAIK there is no way to independently lock conditional formatting but allow other formatting at the same time. It's either both or none.

However, If you record a macro applying the conditional formatting you want to your cells, you can call it with Worksheet_Change, as in this example, to essentially overwrite the existing formatting with the conditional formatting rule you want.

If the issue arrises because your users are copying/pasting into the sheet and messing up the conditional formatting, there's ways around that by forcing a paste special: values instead of regular paste, but if you're worried about just typing in values, the above link is probably best.

 

[Hasson]

it doesn't help it shows error

 

[severynm]

Typo. Is Not instead of IsNot.

 

[Hasson]

it shows another error

 

[Joe4]

However, If you record a macro applying the conditional formatting you want to your cells, you can call it with Worksheet_Change, as in this example, to essentially overwrite the existing formatting with the conditional formatting rule you want.

That link seems to have all sorts of errors with it.
In addition to the errors already pointed out, there is also no "ThisWorksheet" module in Excel.
There is a "ThisWorkbook" module, and inidividual sheet modules.

If you want to use something like that, you may want to reach out to Alan Wyatt and ask him if he can revise and correct all the errors in that post.

 

[severynm]

That link seems to have all sorts of errors with it.
In addition to the errors already pointed out, there is also no "ThisWorksheet" module in Excel.
There is a "ThisWorkbook" module, and inidividual sheet modules.

I suppose that's what I get for not verifying the solutions that i link to. :/

@Hasson sorry for the bad link. If that is the angle you want to take, you might try something like this instead

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim conditionalFormatRange As Range
    Set conditionalFormatRange = Me.Range("$B$3:$B$50")
    
    If Not Application.Intersect(Target, conditionalFormatRange) Is Nothing Then
        'call function that sets conditional formatting here from macro recorder
    End If
End Sub