AlwaysLearning2018
Board Regular
- Joined
- Nov 23, 2018
- Messages
- 51
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
[TABLE="width: 1067"]
<TBODY>[TR]
[TD]TD</SPAN>[/TD]
[TD] VD</SPAN>[/TD]
[TD] Port</SPAN>[/TD]
[TD] TN</SPAN>[/TD]
[TD] SecID</SPAN>[/TD]
[TD]Tdesc</SPAN>[/TD]
[TD] Units </SPAN>[/TD]
[TD] Price</SPAN>[/TD]
[TD] OFV</SPAN>[/TD]
[TD] BC</SPAN>[/TD]
[TD]MGC</SPAN>[/TD]
[TD]ApproveSt </SPAN>[/TD]
[TD] PostSt</SPAN>[/TD]
[TD]ReviewSt</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]20190301</SPAN>[/TD]
[TD="align: right"] 20190409</SPAN>[/TD]
[TD="align: right"]123</SPAN>[/TD]
[TD="align: right"]22222</SPAN>[/TD]
[TD="align: right"]123456789 </SPAN>[/TD]
[TD]Matured</SPAN>[/TD]
[TD="align: right"]1000</SPAN>[/TD]
[TD="align: right"]100</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Waiting </SPAN>[/TD]
[TD] Not Posted</SPAN>[/TD]
[TD]Not Reviewed</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]20190301</SPAN>[/TD]
[TD="align: right"]20190409</SPAN>[/TD]
[TD="align: right"]456</SPAN>[/TD]
[TD="align: right"]555</SPAN>[/TD]
[TD="align: right"]987654321 </SPAN>[/TD]
[TD]Matured</SPAN>[/TD]
[TD="align: right"]3000</SPAN>[/TD]
[TD="align: right"]100</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Waiting</SPAN>[/TD]
[TD]Deleted</SPAN>[/TD]
[TD]Not Reviewed</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]20190409</SPAN>[/TD]
[TD="align: right"]20190409</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]888</SPAN>[/TD]
[TD="align: right"]194563278 </SPAN>[/TD]
[TD]Buy</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]0.01</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD] SA </SPAN>[/TD]
[TD]INV</SPAN>[/TD]
[TD]Pending</SPAN>[/TD]
[TD]Posted</SPAN>[/TD]
[TD]Not Reviewed</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]20190409</SPAN>[/TD]
[TD="align: right"]20190409</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD="align: right"]999</SPAN>[/TD]
[TD="align: right"]986547230 </SPAN>[/TD]
[TD]Buy</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]0.01</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD] SA </SPAN>[/TD]
[TD]INV</SPAN>[/TD]
[TD]Pending</SPAN>[/TD]
[TD]Posted</SPAN>[/TD]
[TD]Not Reviewed</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2><COL><COL><COL><COL><COL span=2><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]
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
[TABLE="width: 1067"]
<TBODY>[TR]
[TD]TD</SPAN>[/TD]
[TD] VD</SPAN>[/TD]
[TD] Port</SPAN>[/TD]
[TD] TN</SPAN>[/TD]
[TD] SecID</SPAN>[/TD]
[TD]Tdesc</SPAN>[/TD]
[TD] Units </SPAN>[/TD]
[TD] Price</SPAN>[/TD]
[TD] OFV</SPAN>[/TD]
[TD] BC</SPAN>[/TD]
[TD]MGC</SPAN>[/TD]
[TD]ApproveSt </SPAN>[/TD]
[TD] PostSt</SPAN>[/TD]
[TD]ReviewSt</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]20190301</SPAN>[/TD]
[TD="align: right"] 20190409</SPAN>[/TD]
[TD="align: right"]123</SPAN>[/TD]
[TD="align: right"]22222</SPAN>[/TD]
[TD="align: right"]123456789 </SPAN>[/TD]
[TD]Matured</SPAN>[/TD]
[TD="align: right"]1000</SPAN>[/TD]
[TD="align: right"]100</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Waiting </SPAN>[/TD]
[TD] Not Posted</SPAN>[/TD]
[TD]Not Reviewed</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]20190301</SPAN>[/TD]
[TD="align: right"]20190409</SPAN>[/TD]
[TD="align: right"]456</SPAN>[/TD]
[TD="align: right"]555</SPAN>[/TD]
[TD="align: right"]987654321 </SPAN>[/TD]
[TD]Matured</SPAN>[/TD]
[TD="align: right"]3000</SPAN>[/TD]
[TD="align: right"]100</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Waiting</SPAN>[/TD]
[TD]Deleted</SPAN>[/TD]
[TD]Not Reviewed</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]20190409</SPAN>[/TD]
[TD="align: right"]20190409</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]888</SPAN>[/TD]
[TD="align: right"]194563278 </SPAN>[/TD]
[TD]Buy</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]0.01</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD] SA </SPAN>[/TD]
[TD]INV</SPAN>[/TD]
[TD]Pending</SPAN>[/TD]
[TD]Posted</SPAN>[/TD]
[TD]Not Reviewed</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]20190409</SPAN>[/TD]
[TD="align: right"]20190409</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD="align: right"]999</SPAN>[/TD]
[TD="align: right"]986547230 </SPAN>[/TD]
[TD]Buy</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]0.01</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD] SA </SPAN>[/TD]
[TD]INV</SPAN>[/TD]
[TD]Pending</SPAN>[/TD]
[TD]Posted</SPAN>[/TD]
[TD]Not Reviewed</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2><COL><COL><COL><COL><COL span=2><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]