conditional formatting

[biglb79]

is there a way I can have a specific row highlight in red if one cell in that row has an X in it? for example, if someone types X in cell N2, I want A2:K2 to highlight in red, and then I'll copy/paste special formulas down to other rows

 

[jtakw]

Hi,

Assuming you mean something like if N2:Q2 has X, highlight A2:K2

CF, use formula:

=COUNTIF(N2:Q2,"X")

 

[biglb79]

I can't get it to work. do I highlight cells A2:K2 then go to conditional formatting>new rule>use formula to determine which cells to format then type the rule in and click on the fill color?

 

[GR00007]

Can formatting be done via a formula? Haven't heard of it...
Would a macro work? If N2 contains an "X" then fill A2:K2 with red, if not, remove fill.

Sub CheckN2()
    Range("n2").Select
    vN2 = ActiveCell.Value
    Range("A2:K2").Select
    If vN2 = "x" Or vN2 = "X" Then
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Else
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
End Sub

 

[biglb79]

thanks! is there a way to edit that macro so that I can use it on all rows? it's always going to be column N that would have the x and columns A:K that would be highlighted

 

[jtakw]

Apologies, left out absolute referencing, should be:

=COUNTIF($N2:$Q2,"X")

 

[jtakw]

it's always going to be column N that would have the x and columns A:K that would be highlighted

That makes it even simpler, so just:

=$N2="X"

 

[jtakw]

Can formatting be done via a formula? Haven't heard of it...

You haven't heard of the Native Excel feature of "Conditional Formatting" aka CF?

Home tab, near middle of Ribbon, "Conditional Formatting"

 

[biglb79]

that worked perfectly, thanks so much!

 

[jtakw]

You're welcome.