VBA Code to Highlight Rows Based on Multiple Criteria

[AlwaysLearning2018]

Hi All,

I am struggling with writing vba code to highlight rows in certain colors based on the values in a cell, sometimes values based on multiple cells. I have the below spreadsheet, in which I am attempting to highlight rows green where column J (PostSt) has a value of "Deleted" in it. I am trying to highlight rows orange where column J has a value of "Not Posted" in it. Last, I am trying to highlight rows Blue in which column N (ReviewSt) has a value of "Not Reviewed", but also where column J (BC) does not equal "SA" in any value. I have the below code to highlight rows for the criteria I noted above in green and orange, but can't seem to figure out how to now highlight the rows Blue where column N is "Not Reviewed" but also where column J does NOT equal "SA". I was wondering if anyone would be able to help me out and maybe get me pointed in the correct direction? My task is that I must create VBA code to accomplish what I am trying to do, even though I know I can achieve this without. Any help would be so greatly appreciated. Thank you!!

Dim LastRow As Long, c As Range
Dim MyRange As Range
LastRow = Cells(Cells.Rows.Count, "M").End(xlUp).Row
Set MyRange = Range("M1:M" & LastRow)
For Each c In MyRange
If (c.Value) = "Deleted" Then
c.EntireRow.Interior.Color = 5287936
ElseIf (c.Value) = "Not Posted" Then
c.EntireRow.Interior.Color = 49407
Else
c.EntireRow.Interior.Color = xlNone
End If
Next

TD</SPAN>

VD</SPAN>

Port</SPAN>

TN</SPAN>

SecID</SPAN>

Tdesc</SPAN>

Units </SPAN>

Price</SPAN>

OFV</SPAN>

BC</SPAN>

MGC</SPAN>

ApproveSt </SPAN>

PostSt</SPAN>

ReviewSt</SPAN>

20190301</SPAN>

20190409</SPAN>

123</SPAN>

22222</SPAN>

123456789 </SPAN>

Matured</SPAN>

1000</SPAN>

100</SPAN>

0</SPAN>

Waiting </SPAN>

Not Posted</SPAN>

Not Reviewed</SPAN>

20190301</SPAN>

20190409</SPAN>

456</SPAN>

555</SPAN>

987654321 </SPAN>

Matured</SPAN>

3000</SPAN>

100</SPAN>

0</SPAN>

Waiting</SPAN>

Deleted</SPAN>

Not Reviewed</SPAN>

20190409</SPAN>

20190409</SPAN>

1</SPAN>

888</SPAN>

194563278 </SPAN>

Buy</SPAN>

1</SPAN>

0.01</SPAN>

0</SPAN>

SA </SPAN>

INV</SPAN>

Pending</SPAN>

Posted</SPAN>

Not Reviewed</SPAN>

20190409</SPAN>

20190409</SPAN>

2</SPAN>

999</SPAN>

986547230 </SPAN>

Buy</SPAN>

1</SPAN>

0.01</SPAN>

0</SPAN>

SA </SPAN>

INV</SPAN>

Pending</SPAN>

Posted</SPAN>

Not Reviewed</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2><COL><COL><COL><COL><COL span=2><COL><COL><COL><COL><COL><COL></COLGROUP>

 

[Joe4]

Is there some reason that it has to be VBA code?
If not, you should be able to do this with Conditional Formatting (no VBA required).
All things equal, that is usually the preferred way to go.

For example:

I am attempting to highlight rows green where column J (PostSt) has a value of "Deleted" in it.

Let's say that your data is in rows 2:100 (and your header row is in row 1).
Then do the following:
1. Select rows 2:100
2. Go to Conditional Formatting
3. Select the Formula option
4. Enter this formula: =$J2="Deleted"
5. Select the Green Fill format option
6. Click OK

This should highlight all rows with "Deleted" in column J green.

If you have more than one condition, just use AND (if all listed conditions must be met) or OR (if only one condition must be met).
That structure would look like:
=AND(condition1, condition2, ...)

 

[AlwaysLearning2018]

Hi Joe4,

Yes VBA is what I'm asked to create this with. Reason is this task is supposed to be setup basically as automatic as possible with little to no human intervention. This spreadsheet that I referenced I have already written several other vba codes for other conditions.

 

[Joe4]

Reason is this task is supposed to be setup basically as automatic as possible with little to no human intervention.

If that is really the case, Conditional Formatting is actually better, as it is automatic/dynamic.
VBA requires something to call it to run (either manually or based on events happening). So there are situations in which VBA may not run. But there are not any situations in which Conditional Formatting would not be applied!

If there is VBA running to populate or format the data "on-the-fly", then it would be better to have your VBA add the Conditional Formatting rules, instead of trying to create VBA code simulate Conditional Formatting.
You can get most of the VBA code you need to set up Conditional Formatting by turning on your Macro Recorder and record yourself performing the Conditional Formatting steps manually.

 

[AlwaysLearning2018]

Thanks for the explanation. I disagree with nothing you stated. The issue is The VBA code writing nonsense is a job request. I'll continue to figure it out on my own. Just thought someone on here may be able to give some guidance on the vba code aspect. Obviously definitely not anyone's task to give me free help.

 

[Joe4]

The issue is The VBA code writing nonsense is a job request. I'll continue to figure it out on my own.

You can still have VBA do it - just have VBA create the Conditional Formatting Rules like I suggested in the previous request. Using the Macro Recorder should give you most of the code you need without actually having to write it.

Otherwise, what you are doing is trying to have VBA emulate/simulate conditional formatting. Why do that when you can just tell VBA to do Conditional Formatting?
It would be like trying to create the addition function in VBA, instead of just telling VBA to use the existing addition function.

If I still haven't convinved you, and you really want to tell VBA to do it, without VBA setting up Conditional Formatting, let me know and I can show you how to do that.
I would just need to know one thing: How will this VBA be called to run? Do you want an event procedure to run it based on some event happening? Or will it be called manually or by another VBA procedure?
The answer to that impacts how we set it up.

 

[AlwaysLearning2018]

Hi Joe4

Thanks again. Can you please assist me with telling VBA to do so? The process will be initiated by going into the developer tab, selecting the macro I already have setup for either pieces of the process, and clicking run.

 

[Joe4]

Try this:

Sub MyMacro()

    Dim LastRow As Long
    Dim r As Long

    Application.ScreenUpdating = False

    LastRow = Cells(Cells.Rows.Count, "M").End(xlUp).Row
    
    For r = 1 To LastRow
        If Cells(r, "M") = "Deleted" Then
            Rows(r).Interior.Color = 5287936
        Else
            If Cells(r, "M") = "Not Posted" Then
                Rows(r).Interior.Color = 49407
            Else
                If (Cells(r, "N") = "Not Reviewed") And (Cells(r, "J") <> "SA") Then
                    Rows(r).Interior.Color = 12611584
                Else
                    Rows(r).Interior.Color = xlNone
                End If
            End If
        End If
    Next r
    
    Application.ScreenUpdating = True

End Sub

 

[AlwaysLearning2018]

Thank you very much. This is perfect and I very much appreciate you taking the time to write this.

Best Regards

 

[Joe4]

You are welcome.