CODEWITHSUNDEEP
excelconditional-formatting
fid
fid

Reputation: 72

Conditional Formatting row wise

There is a cell I want to format if two cells in a row meet conditions:

When cell F4 equals one of the workers and the max. hours of the same worker is > 10 cell F4 should be red.

I tried this but didnt get the rule working.

enter image description here

Upvotes: 0

Views: 167

Answers (3)

Tomasz
Tomasz

Reputation: 426

try worksheet change event. u can easly add borders, font etc in vbe worksheet with data paste

Dim int_fcol, int_frow, int_lcol, int_lrow As Integer
Dim rng_WorkRange As Range
Private Sub Worksheet_Activate()

int_fcol = ActiveSheet.UsedRange.Column
int_frow = ActiveSheet.UsedRange.Row
int_lcol = ActiveSheet.Cells(int_frow, Columns.Count).End(xlToLeft).Column
int_lrow = ActiveSheet.Cells(Rows.Count, int_fcol).End(xlUp).Row

'MsgBox "first columns is: " & int_fcol & vbCrLf & _
"first row is: " & int_frow & vbCrLf & _
"last column is: " & int_lcol & vbCrLf & _
"last row is: " & int_lrow
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim int_ColumnWithHours As Integer
'   column with hours
int_ColumnWithHours = int_fcol + 1

If Target.Column = int_ColumnWithHours Then
    '   setting table range
    Set rng_WorkRange = ActiveSheet.Range(ActiveSheet.Cells(Target.Row, int_fcol), ActiveSheet.Cells(Target.Row, int_lcol))
    If Target.Value > 10 Then   '   check does more than 10 hour
        '   if true colour red
        rng_WorkRange.Interior.ColorIndex = 3
    Else    '   If Target.Value > 10
        '   if false white
        rng_WorkRange.Interior.ColorIndex = 0
    End If  '   If Target.Value > 10
End If  '   If Target.Column = int_fcol + 1
End Sub

Upvotes: 0

basic
basic

Reputation: 11968

Use formula in CF:

=SUM(($L$13:$L$15=$F$4)*($M$13:$M$15>10))

and apply to range F4

enter image description here

Upvotes: 1

RipRapRob
RipRapRob

Reputation: 65

Do this:

  • Mark all the cells you want to be colored IF the rule applies
  • Make your rule as it would look for the TOP left cell/row

Look at this example:

enter image description here

So in this case, i would like the rule for the first row to be: Color red if cell F4 > 10.

Result:

enter image description here

Upvotes: 1

Related Questions