prevent clear color by condition formatting

Hasson

Board Regular
Joined
Apr 8, 2021
Messages
87
Office Version
  1. 2016
Platform
  1. Windows
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?
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

severynm

Board Regular
Joined
Jan 8, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Try protecting the worksheet, making sure that "Format cells" is unchecked.
 

Hasson

Board Regular
Joined
Apr 8, 2021
Messages
87
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jan 8, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 8, 2021
Messages
87
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

it doesn't help it shows error
1.PNG
 

severynm

Board Regular
Joined
Jan 8, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Typo. Is Not instead of IsNot.
 

Hasson

Board Regular
Joined
Apr 8, 2021
Messages
87
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

it shows another error
1.PNG
 

Joe4

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

Board Regular
Joined
Jan 8, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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
 

Forum statistics

Threads
1,136,954
Messages
5,678,755
Members
419,782
Latest member
gc75150

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