help with conditional formatting (1 Viewer)

[associates]

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

 

[Brianwarnock]

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

 

[shades]

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.
________
FERRARI CLUB OF AMERICA HISTORY

 

[associates]

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

 

[shades]

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.
________
Silver surfer reviews