Color cells based on values

[paddy69]

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).

[Bob Phillips]

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

[blackie42]

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

[mdmackillop]

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

[Bob Phillips]

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

[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?

[blackie42]

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

[Bob Phillips]

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.?



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


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.

[Bob Phillips]

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?



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

[blackie42]

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

thanks again

jon