Hi JLatham

That works great, but it formats all rows - I need it to only format rows
where the string in column A matches to the value in cell A3. If it matches
then the formatting happens to that row.

I'll then repeat the macro but for A4, A5, A6 (and applying a different
interior colour) - unless you know of how to do that automatically in the
macro??

--
Thank for your help
BeSmart


"JLatham" wrote:

Why not just use conditional formatting? Here's code that would allow you
define a range by changing a few variables in it.

See if this works for you...

Sub Decorate()
'set cells in columns C:S that have a value
' Greater Than 0 to .ColorIndex 41, solid
Const firstRowToFormat = 2
Const firstColToFormat = "C"
Const lastColToFormat = "S"
Dim lastRowToFormat As Long
Dim tmpString As String
Dim rngB As Range

tmpString = Worksheets("Sheet2").UsedRange.Address
lastRowToFormat = _
Range(Right(tmpString, Len(tmpString) - InStr(tmpString, ":"))).Row

Set rngB = Worksheets("Sheet2"). _
Range(firstColToFormat & firstRowToFormat & ":" & _
lastColToFormat & lastRowToFormat)

'for Operator, use xlLess if you need Less Than condition
With rngB
.FormatConditions.Delete
.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="0"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Interior.ColorIndex = 41
.FormatConditions(1).Interior.PatternColorIndex = _
xlAutomatic
End With
Set rngB = Nothing
End Sub


"BeSmart" wrote:

Hi All
I have a code below that formats cells - but it formats all cells in the row
& I need it to only format cells 0 in the row.
I tried a few things and got errors .... How do I incorporate the additional
If Then Else into this code?

Sub Decorate()
Dim rngB As Range
Dim rngTemp As Range
Set rngB = Sheets("Sheet2").UsedRange.Columns("A:S")
For R = 1 To rngB.Rows.Count
If Sheets("Sheet2").Cells(R, 1).Value = Range("A3") Then
Set rngTemp = Sheets("Sheet2").Cells(R, 1).Range("C1:S1")

'''' If rngTemp.Value <1 Then (I tried adding this - but got a "type
mismatch" error???)
''' Else

rngTemp.Font.Bold = True
With rngTemp.Interior
.ColorIndex = 41
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
'''' End If
End If
Next R
End Sub
--
Thank for your help
BeSmart