conditional formatting in excel problem - help in formula

1
votes

I need a macro to be used in conditional formatting. I need that if a row contains a cell that is 1, then i need that the whole row is highlighted (e.g. A1 - E1).

at the moment i only managed to highlight the cells containing number 1, but not the whole row. any ideas ?

thanks in advance

1
excelexcel-formula

1 Answers

3
votes

To do it in Excel, select A1:E1, then Format > Conditional Formatting... > Formula Is > =SUMIF($A1:$E1,"=1")>0. Don't forget the $ dollar signs, to specify an absolute (and not a relative) reference to columns A to E!

This is the VBA code to do the same thing in a macro:

With Range("A1:E1")
    ' If you need to delete any "pre-existing conditions" 
    ' (no US healthcare reform pun intended) then uncomment the following line: 
    '.FormatConditions.Delete 

    .FormatConditions.Add Type:=xlExpression, _
         Formula1:="=SUMIF($A1:$E1,""=1"")>0"
    .FormatConditions(1).Interior.ColorIndex = 6 ' yellow background
End With