Can I use an If statement to see if a cell is already conditionally formatted?

TedX

Board Regular
Joined
Apr 18, 2021
Messages
122
Office Version
  1. 365
Platform
  1. Windows
The image below shows that I have a conditional format on cell AC4, unfortunately when I run my macros at the end of every day and set up for the next day, that cell creates a second identical format. I just keep going there and deleting it manually, but I was wondering if I could just use an 'If' statement to see if there was already some conditional formatting there, then just leave it and not create a duplicate. Any ideas on how to structure the If statement, or even some other workaround like, clearing out the original and then just letting the rest of the code do it;s thing as there wouldn't be any duplication then, if the original formatting was deleted first.

1673782950873.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
There is no way to determine if a cell has conditional formatting applied, nor is there a formula to determine if a cell has Data Validation applied. I mention that because I've posted a request for a function ISDATAVALIDATION on the Microsoft Excel Feedback Community board, and hasn't received any votes. Join and vote please! And maybe request for a ISCONDITIONALFORMAT function. I'd vote for that!
That said, I am NOT a fan of VBA nor am I at all fluent in it, but that is the only way to determine whether or not a cell has Conditional Formatting applied. Not knowing where to even start with a VBA script to do that, I "cheated" and got ChatGPT to answer the question! Here's what it came up with:
VBA Code:
Sub CheckConditionalFormatting()
    Dim rng As Range
    Set rng = Range("A1")

    If rng.FormatConditions.Count > 0 Then
        MsgBox "Cell A1 has conditional formatting applied."
    Else
        MsgBox "Cell A1 does not have conditional formatting applied."
    End If
End Sub
It went on to say:
This code creates a Range object for cell A1, and then checks the FormatConditions property of that range to see if it has any conditions applied. If the Count property of the FormatConditions object is greater than 0, it means that the cell has at least one condition applied and the message box will display "Cell A1 has conditional formatting applied." If the Count property is equal to 0, it means the cell does not have any conditional formatting and the message box will display "Cell A1 does not have conditional formatting applied."

You can also loop through the FormatConditions collection to check the specific condition applied to that cell, like this :

VBA Code:
For Each fc in rng.FormatConditions
    Debug.Print fc.Formula1
Next
This will give you the formula used in that conditional formatting.

Hope that helps!
 
Upvote 0
Thank you jdellasala, I used your code and got the msgBox, then changed it for clearing the cell, but that actually led me down a slightly different path, so I put it back the way it was, then thanks to your suggestion, found a way of 'Not' copying the code in the first place, meaning no duplication would result. So thanks to your prompting me, I have resolved the issue. Thank you so much!!! (y)
 
Upvote 0
You can check if a cell has conditional formatting applied like
VBA Code:
   With Range("AC4")
      If .Interior.Color <> .DisplayFormat.Interior.Color Then
         MsgBox "CF exists"
      End If
   End With
This just checks the fill colour although it can be expanded.

Also the code from jdellasala only tells you if there is a CF rule for that cell, not whether that rule is active & has coloured the cell.
 
Last edited:
Upvote 0
Solution
Thanks Fluff, I have that part sorted now and appreciate you watching out 🙏 I do have a follow-up question in the same area however. I have 2 beautifully working macros, which I activate via rectangles and because they are copied each day, without being deleted, I get an extra set of rectangles. The only reason I have them in the first place is to assign the macros to them, they are Next and Reset. Can I just Type into the U5 and W5 cells, the Words, Next and Reset and somehow assign those cells to the macros?

1673794677282.png
 
Upvote 0
You could use a double_click or Right_click event code, but as that is a different question it needs a new thread.
 
Upvote 0
You could use a double_click or Right_click event code, but as that is a different question it needs a new thread.

I understand and thank you again 🙏
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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
Back
Top