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

DerbyDad03 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.

Hi. I meant to mark in yellow the row in table. Any idea? thanks

DerbyDad03 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.

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

DerbyDad03 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.

Thanks you!!! It did it!!!