How can I conditionally format an entire row in Excel based upon the data in one of the columns in that row?

February 1, 2008 5:33 PM

How can I conditionally format an entire row in Excel 2007 based upon the contents of a particular column in that row?

Say row 1 column J, row 4 column J, and row 19 column J all contain "Alice." I'd like rows 1, 4, and 19 all to appear with a red background. Say row 2 column J, row 44 column J, and row 45 column J all contain "Bob." I'd like for rows 2, 44, and 45 all to appear with a blue background. And so forth.

Basically, what I'm asking is how to make a relative column reference in a formula. I remember doing precisely this using VBScript for Excel years ago, but for the life of me can't locate that old code. Anyway, something along the lines of the following psuedo-code entered into the Conditional Formatting dialog box:

Apply "Format 1" according to the following formula [assuming (y,x) refers to (column y, row x)]:

=IF((J,ROW())="Alice")

It might also be nice to do this with a named range, so that if I apply the name "First_Name" to the entirety of row J, the formula is then a bit clearer:

=IF((First_Name,ROW())="Alice")

Total n00b0 question, I'm sure, but I don't spend much time in Excel, and am would very much appreciate the help. Thanks!

posted by ChasFile to computers & internet (8 comments total)

You're overthinking the problem. Cell references in a conditional formatting formula work just like you'd expect. For example, select all of the cells in a worksheet and apply a conditional format with a formula of =($J1="Alice"). After applying this conditional format if you now go and examine the conditional formating of a particular cell, for instance cell C3, you'll see that it has a condition formatting formula of =($J3="Alice").
posted by RichardP at 6:01 PM on February 1, 2008 [1 favorite]

Nice one RichardP. I've always thought the conditional format windows was a bit crap w/r/t this problem, but now I know.

For reference, my solution is the following VBA:

Sub cond_format()

For Each r In ActiveSheet.Rows
If Cells(r.Row, 10).Value = "Alice" Then Rows(r.Row).Font.Bold = True
Next

End Sub


but RichardP's answer looks faster and easier.
posted by pompomtom at 6:09 PM on February 1, 2008

RichardP:

I tried what you suggested in a couple test cases, but I'm still having problems. The basic thrust of your solution seems to, at least, behave as I'd like it to, in that it seems to highlight entire rows based upon the content of a particular column in that row. However, while the formatting behaves correctly if the column contains integers, it doesn't seem to behave correctly when I use a string, as I'd like to. Can you take a look at that file I linked above and tell me if there is something I'm doing wrong?
posted by ChasFile at 11:09 PM on February 1, 2008

pompomtom: thanks, that VB is pretty much exactly what I was thinking about in the OP, and that what really helpful for translating my psuedo-code. I'm just a bit rusty with my VB syntax.
posted by ChasFile at 12:00 AM on February 2, 2008

ChasFile, I'd be happy to look at your file, however I can't open a ".xlsx" (I have not upgraded to Office 2008 Mac). If you'd reupload your file as an .xls file, I'll look at your formulas.

In the meantime, as an aid, I've uploaded an example Excel spreadsheet that applies conditional formatting to entire rows based on the values of strings in a column.
posted by RichardP at 12:37 AM on February 2, 2008

Try ASAP utilities

Free, fast, useful.
posted by omegar at 7:07 AM on February 2, 2008

Here's what I ended up doing, thanks in part to pompomtom's help:

Sub Format_By_Row()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With Sheets("Data")

        'Select the sheet so we can change the window view
        .Select

        'Go to normal view for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, again for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'Loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'Check the values in the J column in this example
            '[Note: alternatively, if whole row is named,
            'could use that name if it makes things clearer:
            'e.g. With .Cells(Lrow, "First_Name")
            'Also With .Cells(Lrow, 10) works, too, if row/column math desired.
            With .Cells(Lrow, "J")
            
                'Safety first
                If Not IsError(.Value) Then
                    
                    'Do tests and set BG colors appropriately
                    Select Case .Value
                            Case "Alice"
                                Rows(Lrow).Interior.Color = RGB(255, 0, 0)
                            Case "Bob"
                                Rows(Lrow).Interior.Color = RGB(255, 153, 0)
                    End Select

              End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

posted by ChasFile at 7:27 AM on February 2, 2008

Though my solution is probably not the cleanest (again, what n00b-ish VBA skills I ever once had are pretty rusty) solution, note, however, that pompomtom's cleaner quicker solution - for me, at least, formatted every cell on the spreadsheet, including the unused/blank ones down in row 64,000, leading to long execution times for the script and quite large files. Hence my use of the UsedRange object, and also the counting backwards (rather than forwards toward and possibly into those unused rows) For loop.

Thanks for the help, though. I'll definitely take a look at RichP's conditional formatting example, as if it could be done in one equation in a dialog box rather than dozens of lines of VBA, that'd be a plus, and I'm also interested to check out this ASAP add-on.
posted by ChasFile at 7:47 AM on February 2, 2008