Hi,

I was wondering if i could get some help on some issues. I'll post two questions separately as they are different questions.

First question is I don't know if this is possible in excel. I know that we can apply conditional formatting on a single cell. is it possible to do in a range of cells.

For example, in worksheets "Data"

A1 = 20
B1 = 30
C1 = 15

A20 = 20
B20 = 30
C20 = 15

if i come along and change the value of A1 from 20 to 30, i want the whole row (in this case, row 1) to change its color to red or something to get attention of users.

is it possible? if it's, how do i do it?

Thank you in advance

Yes it is simple
Select the row; select conditional formating; select formula is ;write your formula but use absolute addressing on the column eg =$a1=20 select your format, complete the OR formats by selecting ADD button, there is presumably a limit to the number but I don't know it.

Brian

Limit is three (although with original formatting it gives four conditions).

If you want more than that, I have some code you can use in VBA.

Thank you Brian and Shades for your reply. But i don't know how to apply it to my existing code. It's my mistake not to tell you in advance.

Anyway, here is my code

For i = 0 To (UBound(x))
c$ = Chr$(65 + i) + Trim(Str$(Count))

With Worksheets("Data")
With .Range(c$)
'With myRange
.FormatConditions.Delete
'.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=my" & c$
'.FormatConditions(1).Interior.ColorIndex = 36
.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=my" & c$
.FormatConditions(1).Interior.ColorIndex = 42

End With
End With
Next i

As you may notice, it checks against the other worksheet i.e. Result. So in my worksheet Result, i defined name for all the cells.

So far, what i could get to work is only a single cell whose value is changed. Because it's different from worksheet Result.

How would i change my code so that it would highlight the whole row if the value in one of the cells gets changed?

Thank you in advance

Don't use FormatConditions. Rather use Select Case:


Select Case CellValue
Case Is <= 0
' Color
Cell.ColorIndex = 2
Case Is <= 1
' Color
Cell.ColorIndex = 3
Case Is <= 2
Cell.ColorIndex = 4
Case Is <= 3
Cell.ColorIndex = 5
Case Else
Cell.ColorIndex = 6
End Select



This is only a sample. Try that as the determining condition.