Forum Discussion

RobG92's avatar
RobG92
Copper Contributor
Jun 10, 2018

How to remove conditional format in specific cells via Macro?

Hello, I'm trying to figure out how to remove conditional formatting in specific cells. We have 9 columns. In Column 8, the header is called "Status" Among the status' there will be the occas...
excel
Formulas and Functions
Macros and VBA
Russell Proctor's avatar
Russell Proctor
Brass Contributor
Jun 13, 2018

Hi,

You can remove conditional formatting from individual cells by clearing the formats on those cells.

 

Dim lrowno As Long
For lrowno = 2 To 10
    If (Range("H" & lrowno) = "Rework") Then
        Range("A" & lrowno & ":G" & lrowno).ClearFormats
    End If
Next lrowno
If you try and use the FormatConditions(1) associated with the cells you will get a "Type Mismatch" VBA error.
 
Hope this helps
Russell
https://bettersolutions.com/excel.htm

 

RobG92's avatar
RobG92
Copper Contributor
Jun 13, 2018
You're on the right track!!

I am not proficient enough to understand those "Dim" stuff.
But I tried to remake it for my use and it worked for one row but stopped there. On any given day we might have 3 or more labeled "Rework" in a list of hundreds of rows. But it worked for one row specifically.

Is there a way to get it to work in the same vein as conditional formatting? Whee every time a new cell in column H is labeled "Rework" it clears the formatting for the row?
  • Russell Proctor's avatar
    Russell Proctor
    Brass Contributor
    Jun 13, 2018

    Hi Robert,
    The following line of code tells the macro how many rows to consider.

    For lrowno = 2 To 10
    This is telling it to loop for row numbers 2,3,4,5,6,7,8,9 and 10.
     
    It is possible to have it happening automatically in a similar way to conditional formatting.
     
    One solution is to use a Worksheet Event:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oRange As Excel.Range
    Dim oColorScale As Excel.ColorScale

        If ((Target.Column = 8) And (Target.Cells.Count = 1)) Then
            If (Target.Row <= 2) Or (Target.Row <= 10) Then
                Set oRange = Range("A" & Target.Row & ":G" & Target.Row)
                If (Target.Value = "Rework") Then
                    oRange.ClearFormats
                Else
                    oRange.ClearFormats
                    Set oColorScale = oRange.FormatConditions.AddColorScale(2)
                    oColorScale.ColorScaleCriteria(1).FormatColor.Color = RGB(204, 223, 244)
                    oColorScale.ColorScaleCriteria(2).FormatColor.Color = RGB(128, 176, 228)
                End If
            End If
        End If
    End Sub 

     

    If you would like to find out more please contact me via the website:

     

    Hope this helps

    Russell

    https://bettersolutions.com/excel.htm

     

     

Resources