Conditional formatting applied to cell - yes or no

[Kasbres]

I need to know how to find if a cell has conditional formatting applied, or the reverse, there is no conditional formatting applied. In Cell a2 enter yes if Cell b2 has conditional formatting if not enter no. Ultimately The A2 will be used in other formulas, to count how many yeses there are overall, by person by department, etc cells with Conditional formatting will be moved to other worksheets based on criteria.

I have searched and read so many threads and tried so many formulas but I just don't get it or it is right in front of my face and I am putting it in the wrong place in VBA.

I understand that I will have to use VBA but I am a newbie to that. I have seen to use UDF and not to use UDF. Took me a hours to figure out what UDF even was.

I need a dummies version. Please Please help me.

 

[oldbrewer]

are you basically just wanting to count yesses ?

 

[Fishboy]

I need to know how to find if a cell has conditional formatting applied, or the reverse, there is no conditional formatting applied. In Cell a2 enter yes if Cell b2 has conditional formatting if not enter no. Ultimately The A2 will be used in other formulas, to count how many yeses there are overall, by person by department, etc cells with Conditional formatting will be moved to other worksheets based on criteria.

I have searched and read so many threads and tried so many formulas but I just don't get it or it is right in front of my face and I am putting it in the wrong place in VBA.

I understand that I will have to use VBA but I am a newbie to that. I have seen to use UDF and not to use UDF. Took me a hours to figure out what UDF even was.

I need a dummies version. Please Please help me.

Hi Kasbres, welcome to the boards.

The following is a simple VBA check to see if a cell has conditional formatting applied to it.

- Press ALT+F11 to open the VBA Developer window
- Right-click on ThisWorkbook and select Insert->Module
- In the new window that opens simply copy / paste in the following:

Sub CheckForCF()
    If ActiveCell.FormatConditions.Count > 0 Then
        ActiveCell.Offset(0, -1).Value = "Yes"
    Else
        ActiveCell.Offset(0, -1).Value = "No"
    End If
End Sub

- The above code can be run by selecting the cell you want to check (in your example, B2), then press ALT+F8 and select the CheckForCF macro from the list.

Basically the code counts how many format conditions are applied to the ACTIVE CELL (i.e B2) and if the count if greater than zero it will put "Yes" in the preceding cell, else it will put "No" in the preceding cell.

If you are happy that this is doing what you need then we can try and expand the range that it looks at to be more than just a single cell.

 

[Fishboy]

This version of the code will check each cell in the specified range and update the preceding cell with "Yes" or "No" accordingly:

Sub CheckRangeForCF()
Dim Cell As Range, cRange As Range
    Set cRange = Range("B1:B100")
        For Each Cell In cRange
            If Cell.FormatConditions.Count > 0 Then
                Cell.Offset(0, -1).Value = "Yes"
            Else
                Cell.Offset(0, -1).Value = "No"
            End If
        Next Cell
End Sub

This version of the code will check each cell in the specified range and output a message box with the count of cells containing conditional formatting:

Sub CheckRangeForCFMessage()
Dim Cell As Range, cRange As Range, cfCount As Integer
    Set cRange = Range("B1:B100")
        For Each Cell In cRange
            If Cell.FormatConditions.Count > 0 Then
                cfCount = cfCount + 1
            End If
        Next Cell
    MsgBox cfCount
End Sub

You can update the bold red range to suit which cells you want to check.

 

[Kasbres]

Thank you

Thank you all, so much.

Fishboys first option is closest to what I need, I think, I put in VBA Module, then ran the Macro it but all the A cells come back yes. I checked and there is no conditional formatting on B3 and B5.

Again I soooo appreciate this.

I just feel so stupid with this. What am I doing wrong.

 

[oldbrewer]

I struggle to see why you want to count cells with conditional formatting. Because the conditional formatting equation is only determining , for example, if G1>100. Conditional formatting will eg turn G1 red. You need to use a "helper cell" to see if G1 has conditional formatting applied. I cannot see why I would ever need to know how many cells in my worksheet are conditionally formatted. If I want to know how many cells in G column are greater than 100 there are many ways to see this using only one cell with a formula in it. I think there is more to this than your posts describe, possibly.

 

[Fishboy]

Thank you

Thank you all, so much.

Fishboys first option is closest to what I need, I think, I put in VBA Module, then ran the Macro it but all the A cells come back yes. I checked and there is no conditional formatting on B3 and B5.

Again I soooo appreciate this.

I just feel so stupid with this. What am I doing wrong.

Are you able to share a copy of your workbook with us? If so you would need to upload a copy to a file hosting site like Drop Box, One Drive or similar, then post a link to the file here. I am unsure why you would get a Yes response back from all cells.

It is also worth noting that OldBrewer's point about simply counting the criteria that would flag the conditional formatting in the first place is probably a far simpler solution. What are the conditions / criteria that should flag whether or not a cell is to be conditionally formatted?

 

[Kasbres]

I don't want to count cells with conditional formatting, I want to evaluate a cell B2 and if it has conditional formatting, put yes or no in A2. That will then allow me to use A2 as part of other formulas where if a2 is yes then... The spreadsheet I have has about 15 conditions . I cannot take all the conditions into a formula and then add to it. One of the formulas I need for example is, on another tab worksheet2, if a2 is yes, then move b2-as2. On Worksheet 2 then remove the blanks. I have at least three other formulas that can use this criteria. This is reality is not 1 document, this is really 31 that each person has, and doing this manually every month is killing me.

 

[Kasbres]

Fishboy: I will have to change some things before I can post it. It will be later today before I can get to it. So sorry, and thank you so much for your help.

 

[oldbrewer]

"I want to evaluate a cell B2 and if it has conditional formatting, put yes or no in A2. That will then allow me to use A2 as part of other formulas where if a2 is yes then... I want to evaluate a cell B2 and if it has conditional formatting, put yes or no in A2. That will then allow me to use A2 as part of other formulas where if a2 is yes then... "

So A2 is yes or no depending if B2 is conditionally formatted - but the formula in B2 is either checking the value in B2 or values in other cells. So cut out the middle man and make A2 yes or no dependent on the formula. Simple example B2 turns red if B2 is >100. in A2 =if(B2>100,"yes","no")