Color cells based on values

In my sheet I would like to color all cells in the rows starting with "Rep:" (column A) red if the value in column F until the end is greater than 168 and green if the value of these cells is greater than or equal to 0. Nothing should change if the cell is empty.
I have manually colored the first row (row 5) as an example (see attachment).

You need conditional formatting. See http://www.contextures.com/xlCondFormat01.html

I was looking at this too and trying to get a whole column to conditionally format the #DIV/0! but can't seem to get it to work.

Condition 1:
Formula Is =ISERROR(C2) (White Font)

Works fine for the single cell but should this work for a range e.g.

Condition 1:
Formula Is =ISERROR(C:C) (White Font)

I have a book where formulas are in columns in the sheet code. When i copy and paste infos in to the sheet not all the columns are populated with figures which returns a lot of #DIV/0!s which I have to blank manually. The above would be very useful if it worked?

Any ideas?

thanks

Hi Blackie,
Set it for one cell, then use Format Painter to apply it to the column

No, select the whole column starting at C2, then add your first formula.

I tried conditional formatting but that didn't work because it's only applicable for those rows that start with "Rep:". So I need VBA to find these rows in my report (it's a dynamic report) and then set the conditional formatting for all columns from F until the end of the report.

Any suggestions?

Seems to work by installing it in one cell and dragging down.

Is there any way this can be installed in to a worksheet change event for specific columns e.g. cols J, S & U in sheet 1.?

Would be better than having to continually copy the formatting.

thanks for help

Jon

Quote:

---

Originally Posted by blackie42

Is there any way this can be installed in to a worksheet change event for specific columns e.g. cols J, S & U in sheet 1.?

---

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.FormatConditions.Delete
.FormatConditions.Add _
Type:=xlExpression, _
Formula1:="=ISERROR(" & .Address(False, False) & ")"
.FormatConditions(1).Interior.ColorIndex = 3 'Red
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

Quote:

---

Originally Posted by paddy69

I tried conditional formatting but that didn't work because it's only applicable for those rows that start with "Rep:". So I need VBA to find these rows in my report (it's a dynamic report) and then set the conditional formatting for all columns from F until the end of the report.

Any suggestions?

---

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column

With .Cells(i, "F").Resize(, LastCol - 5)
.FormatConditions.Delete
.FormatConditions.Add _
Type:=xlExpression, _
Formula1:="=LEFT($A" & i & ",4)=""Rep:"""
.FormatConditions(1).Interior.ColorIndex = 3 'Red
End With
Next i

End With

End Sub
[/vba]

Thanks for help XLD - can't close Thread as I pretty much hi-jacked it.

thanks again

jon