Solved VBA Conditional Formatting Entire Row

May 29, 2019 at 10:24:32
Specs: Windows
Ive done this with conditional formatting and have it working without issue. The sheet does, however, have a lot of individual rules for each cell and it seems to create duplicates for every new row. Its getting complicated and confusing to identify a rule easily, so I would like to make these rules in VBA.

Here is what I need.

If cell in column I contains "Yes", then entire row from column A:P turns green.

If column I contains "No", then entire row from column A:P turns red.

If coulmn I contains "N/A", then entire row from column A:P turns yellow.

Help would be greatly appreciated. Thank you.


See More: VBA Conditional Formatting Entire Row

Reply ↓  Report •

✔ Best Answer
May 31, 2019 at 07:35:23
Try ths:

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if a single cell in Column I was changed
  If Target.Column = 9 And Target.Cells.Count = 1 Then

'Set fill color based on contents of changed cell
    If Target = "Yes" Then
      Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 4
    ElseIf Target = "No" Then
      Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 3
    ElseIf Target = "N/A" Then
      Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 6
    Else: Range("A" & Target.Row & ":P" & Target.Row).Interior.Color = xlNone
    End If

  End If

End Sub

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



#1
May 29, 2019 at 12:06:14
I need a little clarification.

First, IMO, you shouldn't use the term "entire row" unless you actually mean the "entire row". When you say "then entire row from column A:P turns green" I assume (dangerous) that you mean the range "Ax:Px" (where x is the Row number) should turn green. That would not be considered the "entire row".

Second, is this something that you want to happen when a cell in Column I is changed to one of those values or is this a macro that you want to run against an existing data set?

Third, when you say "I would like to make these rules in VBA", do you mean that you want VBA to actually create Conditional Formatting rules or that you want VBA to fill the specified cells based on the value in Column I? Those are 2 very different things.

Finally, I'm not sure what you find "complicated and confusing". Don't take that the wrong way. ;-) Since I don't know your work process or your data layout, I don't know what is confusing you. I'm just trying to understand the issue better. If I wanted to apply the CF that you described above, I would select Columns A:P and use the CF wizard with the following 3 formulas: =$I1="No" with the proper fill color, =$I1="Yes" and =$I1="N/A".

Does that not work for you? If not, what is the issue with that method? Knowing that might help us understand why you want to use VBA to accomplish your goal.

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


Reply ↓  Report •

#2
May 29, 2019 at 12:46:56
1: I too was unsure how best to word that. I meant more "Every cell in the row that has data". In this case Ax:Px.

2: I already have close to 200 rows of data, but will be consistently adding more rows. I don't mind going back through the 200 rows and inputting in the appropriate values again just to make the code run.

3: VBA should fill background color based off of the value in column 2.

4: Initially, the rules manager was simple. I had 3 rules when this was created. I've been tasked with editing something on the document moving forward, and when doing so, noticed that the rules manager duplicated the rules. I have no idea why or how, but I now have about 120 rules. The result on the sheet is still correct, but the manager is no longer the easiest thing to navigate.

I've uploaded a screenshot for reference.
https://ibb.co/Wg8hhCJ

I'm know this can be easily "fixed" with an edit to the worksheet rules, but I'd prefer to get this into VBA and avoid the potential for a repeat problem.


Reply ↓  Report •

#3
May 29, 2019 at 19:06:10
I can work on some macros tomorrow, but maybe this info will will help with your current problem:

https://www.mrexcel.com/forum/excel...

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


Reply ↓  Report •

Related Solutions

#4
May 30, 2019 at 09:34:56
EDIT: Please see my next post before you implement the suggestions below.

I'll leave it up to you to clean up all of the existing CF rules. Make sure that you delete any rules that are setting the fill color of Columns A:P based on the values in Column I. I think that that should be done before you run any code so that you are sure that the code is filling the cells, not some errant CF rule.

Once you have cleaned up the CF rules, run this code against the existing data. It should set the colors based on the contents of Column I. Note that the code determines the Last Row of data based on Column A. If Column A doesn't contain data all the way down to the bottom of the data set, then choose a column that does.

Sub FormatExisting()

'Determine last row with data in Column A
   lstRw = Range("A" & Rows.Count).End(xlUp).Row

'Loop through data, setting colors based on Column I
     For nxtRw = 1 To lstRw
       If Range("I" & nxtRw) = "Yes" Then
          Range("A" & nxtRw & ":P" & nxtRw).Interior.ColorIndex = 4
       ElseIf Range("I" & nxtRw) = "No" Then
          Range("A" & nxtRw & ":P" & nxtRw).Interior.ColorIndex = 3
       ElseIf Range("I" & nxtRw) = "N/A" Then
          Range("A" & nxtRw & ":P" & nxtRw).Interior.ColorIndex = 6
      End If
   Next

End Sub

Then paste this code into the sheet module for that sheet. From then on, each change to a individual cell in Column I should set the color based on the updated contents.

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if a single cell in Column I was changed
  If Target.Column = 9 And Target.Cells.Count = 1 Then

'Set fill color based on contents of changed cell
    If Target = "Yes" Then
      Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 4
    ElseIf Target = "No" Then
      Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 3
    ElseIf Target = "N/A" Then
      Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 6
    End If

  End If

End Sub

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

message edited by DerbyDad03


Reply ↓  Report •

#5
May 30, 2019 at 09:53:06
Just FYI...the 2nd part of the suggestion offered above does not handle the situation where you change one of the required values to something totally different.

In other words, changing Column I to Yes, No or N/A should always result in the corresponding choice of the three colors requested, but changing a cell in a row that has already been colored to something like "Fred" is not going to "unformat" the range. The fill color will not change, at least not as the code has been written. That situation can certainly be dealt with, but since you didn't mention it, I didn't include code to handle it.

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


Reply ↓  Report •

#6
May 31, 2019 at 07:20:20
Your first and second code worked as intended.

As usual, you bring up a valid point that I did not think about. In the event that someone mistakenly selects the wrong value (I have these values in a drop-down in column I), can you change the code to adjust for the change in selection?

The cell does allow a person to delete their selection and make the cell empty again. In the event they do this, could the code return the row to a "unformat" state?

message edited by Steven4321


Reply ↓  Report •

#7
May 31, 2019 at 07:35:23
✔ Best Answer
Try ths:

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if a single cell in Column I was changed
  If Target.Column = 9 And Target.Cells.Count = 1 Then

'Set fill color based on contents of changed cell
    If Target = "Yes" Then
      Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 4
    ElseIf Target = "No" Then
      Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 3
    ElseIf Target = "N/A" Then
      Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 6
    Else: Range("A" & Target.Row & ":P" & Target.Row).Interior.Color = xlNone
    End If

  End If

End Sub

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


Reply ↓  Report •

#8
May 31, 2019 at 09:05:48
Beautiful. Thank you for your continued help.

Reply ↓  Report •

Ask Question