CODEWITHSUNDEEP
excelexcel-2007
sandalone
sandalone

Reputation: 41749

Color entire row based on the value of 2nd cell in that row?

I have a list in Excel and I need to format rows based on the value in the cell 2 of that row. This is how data looks like

No. | Name | Other data | Other data 2 | Date | Date 2 |

For example, if Name=John Tery => color row as Red, if Name=Mary Jane => color row as Pink, etc.

I tried using conditional formatting, but I did not know how to make this work. I have very little experience with such tasks in Excel.

Can anyone help?

PS. all name are two-word names

Upvotes: 0

Views: 14980

Answers (2)

Tony Dallimore
Tony Dallimore

Reputation: 12413

Edit Rereading the question, I saw that the entire row is to be coloured not just the name. I also decided that if a recognised name is replaced by an unrecognised name, the colour should be removed from the row. The original code has been replaced to address these issues.

I decided I did not care about the answers to my questions because the solution below seems the easiest for any scenerio I could identify.

First you need some method of identifying that "John Tery" is to be coloured red and "Mary Jane" is to be coloured pink. I decided the easiest approach was to have a worksheet NameColour which listed the names coloured as required. So the routine knows "John Tery" is to be red because it is red in this list. I have added a few more names to your list. The routine does not care how many words are in a name.

Worksheet NameColour showing coloured names

The code below must go in ThisWorkbook. This routine is triggered whenever a cell is changed. The variables MonitorColNum and MonitorSheetName tell the routine which sheet and column to monitor. Any other cell changes are ignored. If it finds a match, it copies the standard form of the name from NameColour (delete this statement from the code if not required) and colours the cell as required. If it does not find a match, it adds the name to NameColour for later specification of its colour.

Hope this helps.

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Changed As Range)

  Dim CellCrnt As Variant
  Dim ColLast As Long
  Dim Found As Boolean
  Dim MonitorColNum As Long
  Dim MonitorSheetName As String
  Dim RowNCCrnt As Long

  MonitorSheetName = "Sheet2"
  MonitorColNum = 2

  ' So changes to monitored cells do not trigger this routine
  Application.EnableEvents = False

  If Sh.Name = MonitorSheetName Then
    ' Use last value in heading row to determine range to colour
    ColLast = Sh.Cells(1, Columns.Count).End(xlToLeft).Column
    For Each CellCrnt In Changed
      If CellCrnt.Column = MonitorColNum Then
        With Worksheets("NameColour")
          RowNCCrnt = 1
          Found = False
          Do While .Cells(RowNCCrnt, 1).Value <> ""
            If LCase(.Cells(RowNCCrnt, 1).Value) = LCase(CellCrnt.Value) Then
              ' Ensure standard case
              CellCrnt.Value = .Cells(RowNCCrnt, 1).Value
              ' Set required colour to name
              'CellCrnt.Interior.Color = .Cells(RowNCCrnt, 1).Interior.Color
              ' Set required colour to row
              Sh.Range(Sh.Cells(CellCrnt.Row, 1), _
                       Sh.Cells(CellCrnt.Row, ColLast)).Interior.Color = _
                                         .Cells(RowNCCrnt, 1).Interior.Color
              Found = True
              Exit Do
            End If
            RowNCCrnt = RowNCCrnt + 1
          Loop
          If Not Found Then
            ' Name not found.  Add to list so its colour can be specified later
            .Cells(RowNCCrnt, 1).Value = CellCrnt.Value
            ' Clear any existing colour
            Sh.Range(Sh.Cells(CellCrnt.Row, 1), _
                 Sh.Cells(CellCrnt.Row, ColLast)).Interior.ColorIndex = xlNone
          End If
        End With
      End If
    Next
  End If

  Application.EnableEvents = True

End Sub

Upvotes: 2

Aprillion
Aprillion

Reputation: 22324

if there are only a few names to handle, each conditional-format formula would look like this 

=$B2="John Tery"
  • you need to have selected the affected rows from the top row down (so current active cell is in the 2nd row, not in the last row)
  • absolute reference to column $B means that for all cells in different columns, column B will be tested
  • relative reference to row 2 means that for cell in different rows, its own row will be tested (e.g. for cell A42, the formula will test value of $B42)
  • equality operator = will return either TRUE or FALSE (or an error if any of the arguments are errors) and it has the same use as inside IF conditions...

Upvotes: 3

Related Questions