Getting error in condition formatting using VBA

Question

I am working on a project in which I am comparing column D with column C of sheet("Backend") and the difference is shown in column E (in %). I'd like to highlight the % difference (column E) in RED color, where the difference is less than -10.00% and greater than 10.00%. Then would like to copy those items from column B corresponding each highlighted cell and paste it in sheet("UPDATER") beneath cell A7.

Attached is the screenshot for your reference

Sub check_date()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wsData As Worksheet, Datasht As Worksheet, lRow As Integer


Set wsData = Sheets("UPDATER")
Set Datasht = Sheets("Backend")
lRow = Datasht.Cells(Rows.Count, 13).End(xlUp).Row


wsData.Range("M8:M" & lRow).Interior.ColorIndex = xlNone
wsData.Range("M8:M" & lRow).FormatConditions.Add Type:=xlExpression, Formula1:="=AND(M8>=EOMONTH(TODAY(),-2)+1,M8<EOMONTH(TODAY(),-1))"
wsData.Range("M8:M" & lRow).FormatConditions(wsData.Range("M8:M" & lRow).FormatConditions.Count).SetFirstPriority
 With wsData.Range("M8:M" & lRow).FormatConditions(1).Interior
 .Color = RGB(255, 255, 0)
 .TintAndShade = 0
 End With
 wsData.Range("M8:M" & lRow).FormatConditions(1).StopIfTrue = False
 Range("M8").Select

End Sub

"getting error" is not a very useful description of the exact problem you're having? If there's an error message, what is it, and which line is highlighted when you press "Debug" ? — Tim Williams
– Tim Williams, Commented Sep 5, 2020 at 18:04
1) Datasht.Range("E" & lRow).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=-0.1" 2) Datasht.Range("E" & lRow).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=0.1" these two lines are actually aint doing much, no cell in column E gets highlighted as per the defined condition thus no row is getting filtered as per colour filter and copied to another sheet. — Naina
– Naina, Commented Sep 5, 2020 at 18:59
I'm no expert but it looks like everything you are doing is only to the last row in column E. Should be more like Range("E1:E" & lRow) etc. But I would use a For loop going from start to end checking if its greater than 10% and less than -10%. Then just use standard conditional formatting to change the text colour if you want that. For loop is cleaner, quicker and easier than autofilter in my opinion. — Simon
– Simon, Commented Sep 5, 2020 at 23:15
I have tried using for loop and getting "type mismatch error" i have also attached a screengrab for your reference. — Naina
– Naina, Commented Sep 6, 2020 at 5:23
Tried your loop myself and worked fine. You didn't dim your cell. Try Dim cell As Variant — Simon
– Simon, Commented Sep 6, 2020 at 6:57

Simon · Accepted Answer · 2020-09-12 08:20:11Z

1

Here's what I got. It's a bit of a drastic change but I'm hoping this is actually what you're going for.

Sub formatcondition()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wsData As Worksheet, Datasht As Worksheet, lRow As Integer, My_Range As Range, i As Integer, iRow As Integer, cell As Variant, RowNum As Long, lRowUpdater As Long

Set wsData = Sheets("UPDATER")
Set Datasht = Sheets("Backend")
lRow = Datasht.Cells(Rows.Count, 5).End(xlUp).Row
lRowUpdater = wsData.Cells(Rows.Count, 1).End(xlUp).Row
RowNum = 8 'setting the first row in the UPDATER sheet


Datasht.Range("E1:E" & lRow).Interior.ColorIndex = xlNone 'Reset the color before running
wsData.Range("A8:D" & lRowUpdater + 8).ClearContents 'clear your updater sheet. Remove if not needed.

For i = 1 To lRow
    On Error GoTo Continue
    If Datasht.Range("E" & i).Value < -0.1 Or Datasht.Range("E" & i).Value > 0.1 Then 'If greater than or less than
        Datasht.Range("E" & i).Interior.ColorIndex = 6 'Change the color of affected cells if you need that
        wsData.Range(wsData.Cells(RowNum, 1), wsData.Cells(RowNum, 4)).Value = _
        Datasht.Range(Datasht.Cells(i, 2), Datasht.Cells(i, 5)).Value 'straight copy the values from the cells as it loops rather than using copy/paste
        wsData.Range(wsData.Cells(RowNum, 2), wsData.Cells(RowNum, 4)).NumberFormat = "0.00%" 'change the number format of outputted cells to percentages (if needed)
        RowNum = RowNum + 1 'move to the next row in the output
    End If
Continue:
Resume Nexti
Nexti:
Next i

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

EDIT:

For the date to add a year my version would be just adding to what I gave earlier. Instead we now add an AND function to contain the OR, then checking if the YEAR in the cell is the current year. If you're only wanting this year then we can also forgo the IF statement which was checking that if the current month was January it would incorporate December. But if thats not needed then:

=AND(OR(MONTH(NOW())=MONTH(M8),MONTH(NOW())-1=MONTH(M8)),YEAR(M8)=YEAR(NOW()))

Or

=AND(MONTH(M8)>=MONTH(NOW())-1,MONTH(M8)<MONTH(NOW())+1,YEAR(M8)=YEAR(NOW()))

Both the same length and do the same thing just in different way.

edited Sep 12, 2020 at 8:20

answered Sep 6, 2020 at 8:29

Simon

1,3752 gold badges11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Naina Over a year ago

Thankyou so much simon for your help, i have tested the code its works like a charm when I run it using F8 key , but when i use F5 key it stops and give an error message of TYPE MISMATCH at this line of code. If Datasht.Range("E" & i).Value < -0.1 Or Datasht.Range("E" & i).Value > 0.1 Then 'If greater than or less than

Naina Over a year ago

i think i got the reason, column E (sheet Backend) can also have #NA values which is why it is showing Type Mismatch error.

Naina Over a year ago

to combat this i have used =IFERROR(D1-C1),"") to make the #NA cells blank, can we do something so it should not copy #NA/Blank cell values to UPDATER sheet?

Simon Over a year ago

Ahh I didn't clear the error in case there were more. I only tested against one error so I didn't see the issue. Basically below the Continue: add Resume Nexti Then Nexti: and it will work. Resume tells Excel to continue the code as is. I've updated the answer as well.

Naina Over a year ago

can we add a code to highlight the dates in column M which are occurring this month or occurred previous month. i have tried using conditional formatting but didn't help.

|

Collectives™ on Stack Overflow

Getting error in condition formatting using VBA

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related