Solved How do I make conditional formatting with Macro for the row?

July 15, 2014 at 11:15:08
Specs: Windows 7
Hi. I am looking for a way in Excel to conditional formatting with a Macro for a row and the one above it depends on the value in last cell of row.
Meaning, I have a table which at the end of it (third column) there can be a No or a Yes. If it's a yes, I would like to mark in yellow the whole row and the one above it..
Thank you!

message edited by MimiLevi


See More: How do I make conditional formatting with Macro for the row?

Report •

✔ Best Answer
July 17, 2014 at 05:59:03
Two items that are still not clear:

1 - You didn't supply any Column letters or Row numbers, so the code below assumes that the table you posted begins in A1.

2 - In your original post you said "... at the end of it there can be a No or a Yes". In your example data, the strings used are "no" and "yes". In VBA "No" and "no" are not the same thing, "Yes" and "yes" are not the same thing. The code below was written to accept any combination of upper and lower case strings.

Try this...

Sub Highlight_Yellow()
Dim lastRw, lastCol As Long
'Determine last Row with data in Column A
 lastRw = Cells(Rows.Count, "A").End(xlUp).Row
'Loop through rows
  For srcRw = 2 To lastRw
'Determine last Column in current Row
   lastCol = Cells(srcRw, Columns.Count).End(xlToLeft).Column
'If the last Column = "Yes" or "yes" then highlight
'the current Row and the Row above with Yellow
    If UCase(Cells(srcRw, lastCol)) = UCase("Yes") Then
      Range(Cells(srcRw - 1, "A"), Cells(srcRw, lastCol)).Interior.ColorIndex = 6
    End If
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
July 15, 2014 at 17:07:41
Something is not clear to me...

When you say "I would like to mark in yellow the whole row and the one above it.” do you mean the whole row of your table or the "whole row" meaning the entire row from Column 1 to the last column of the spreadsheet?

Please be specific about the layout of your table and what it is that you want formatted. Specific cells ranges would probably help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 16, 2014 at 00:20:20
Hi. I meant to mark in yellow the row in table. Any idea? thanks

Report •

#3
July 16, 2014 at 04:03:05
I asked for details related to your spreadsheet layout. It's really hard to come up with a solution unless we have some idea of how the spreadsheet is laid out.

For example, is your table in A1:C2, F5:H6, etc.? Is there more than one table that needs to be formatted? The more detail you provide, the better the chance we can come up with a solution on the first try.

All you told us was the end of the table is the "third column" so I'm guessing that the table has 2 rows and 3 columns. However, I don't like trying to provide solutions when I'm guessing at what I'm working with.

Please provide some details, maybe even an example of your data. If you are going to post example data, please click on the following line and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 16, 2014 at 11:59:43
It has 3 columns and many rows. Can be 4 columns but the yes/no is always at the end. I am looking for a method / function so ?I can use thanks

https://www.youtube.com/watch?v=Wl7bf-0uaVQ 	items      no
<a href="https://www.youtube.com/watch?v=-vpDGw0-Jsg" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=-vp...</a>	                client	      yes
<a href="https://www.youtube.com/watch?v=F2zTd_YwTvo" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=F2z...</a>	                 items     yes
<a href="https://www.youtube.com/watch?v=Wl7bf-0uaVQ" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=Wl7...</a>	                items       no
<a href="https://www.youtube.com/watch?v=-vpDGw0-Jsg" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=-vp...</a>	                client	    yes
<a href="https://www.youtube.com/watch?v=F2zTd_YwTvo" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=F2z...</a>	                items	no
<a href="https://www.youtube.com/watch?v=Wl7bf-0uaVQ" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=Wl7...</a>	                items	no
<a href="https://www.youtube.com/watch?v=-vpDGw0-Jsg" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=-vp...</a>	                 client	no
<a href="https://www.youtube.com/watch?v=F2zTd_YwTvo" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=F2z...</a>          	items	yes
<a href="https://www.youtube.com/watch?v=Wl7bf-0uaVQ" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=Wl7...</a>	                 items	yes
<a href="https://www.youtube.com/watch?v=-vpDGw0-Jsg" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=-vp...</a>	                 client	no
<a href="https://www.youtube.com/watch?v=F2zTd_YwTvo" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=F2z...</a>	                items	no
<a href="https://www.youtube.com/watch?v=Wl7bf-0uaVQ" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=Wl7...</a>	                items	no
<a href="https://www.youtube.com/watch?v=-vpDGw0-Jsg" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=-vp...</a>	               client    	yes
<a href="https://www.youtube.com/watch?v=F2zTd_YwTvo" target="_blank" rel="nofollow">https://www.youtube.com/watch?v=F2z...</a>            	items	yes


Report •

#5
July 17, 2014 at 05:59:03
✔ Best Answer
Two items that are still not clear:

1 - You didn't supply any Column letters or Row numbers, so the code below assumes that the table you posted begins in A1.

2 - In your original post you said "... at the end of it there can be a No or a Yes". In your example data, the strings used are "no" and "yes". In VBA "No" and "no" are not the same thing, "Yes" and "yes" are not the same thing. The code below was written to accept any combination of upper and lower case strings.

Try this...

Sub Highlight_Yellow()
Dim lastRw, lastCol As Long
'Determine last Row with data in Column A
 lastRw = Cells(Rows.Count, "A").End(xlUp).Row
'Loop through rows
  For srcRw = 2 To lastRw
'Determine last Column in current Row
   lastCol = Cells(srcRw, Columns.Count).End(xlToLeft).Column
'If the last Column = "Yes" or "yes" then highlight
'the current Row and the Row above with Yellow
    If UCase(Cells(srcRw, lastCol)) = UCase("Yes") Then
      Range(Cells(srcRw - 1, "A"), Cells(srcRw, lastCol)).Interior.ColorIndex = 6
    End If
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
July 17, 2014 at 14:07:02
Thanks you!!!
It did it!!!


Report •

Ask Question