Forum Discussion
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 occasional value added manual called "Rework".
I have other conditional formatting throughout the document and when something is labeled "Rework" I need it to override Cells A-G (Ignoring the formatting to H) and override I so that we can color those cells specifically.
On a given day we might have 5 out of a hundred so I'm trying to figure out how to do this in VBA for our Macro, if possible.
Any ideas?
Help is appreciated
4 Replies
- Hi Robert,
How are you getting on, have you had any luck resolving it?
You can increase the number of rows that will have the conditional formatting removed from by changing the numbers in the line of code that contains the 2 "Target.Row" references.
Regards
Russell 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 lrownoIf you try and use the FormatConditions(1) associated with the cells you will get a "Type Mismatch" VBA error.Hope this helpsRussellhttps://bettersolutions.com/excel.htm- 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?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 SubIf you would like to find out more please contact me via the website:
Hope this helps
Russell
https://bettersolutions.com/excel.htm
