Target dynamic row but static column in VBA

[MrSmith484]

Dear VBA Experts,

Excel newbie here

I could really use some help with thi piece of code I´can´t get to function.

I want the colour of row "x" in column "K" to change given a certain value of ro "x" in column "G" AND a maximum value in row "x" in columns H-J. Here is what i have come up with as a start Thus far, but doesnt work.


Private Sub Worksheet_Change(ByVal Target As Range)
'On Error Resume Next

If Target.Column = 7 And Target.Value = 5 Then
Target.Offset(0, "K").Interior.ColorIndex = 4
ElseIf Target.Columns = "7-9" And Target.Value = 4 Then
Target.Offset(0, 4).Interior.ColorIndex = 2
Else: Target.Offset(0, "K").Interior.ColorIndex = 48

An example:

"G27" has the value "5" and the max value of "H27-I27" is also 5 then "K27" turns red

If "G27" has the value "5" and the max value of "H27-I27" is 2 then "K27" turns green

Else "K27" turns grey.

I know it is possible to solve the problem w conditional formating, but I need to code this in VBA help is much aprreciated!

 

[Fluff]

Hi & welcome to the board.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 7 Then
      If Target.Value = 5 And Application.Max(Target.Offset(, 1).Resize(, 3)) = 5 Then
         Target.Offset(, 4).Interior.ColorIndex = 3
      ElseIf Target.Value = 5 And Application.Max(Target.Offset(, 1).Resize(, 3)) = 2 Then
         Target.Offset(, 4).Interior.ColorIndex = 4
      Else
         Target.Offset(, 4).Interior.ColorIndex = 48
      End If
   End If
End Sub

 

[MrSmith484]

Thanks, it Works! However is it possible to make it Work if you make the change in any of the columns 7-10 and not just column. As the code Works now, it is red if e.g "G27" is 5 and max"H27:J27" is 5. If I then change column "H27:J27" the colour does not change accordingly, asthe code only resonds to changes in column "G".

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("G:J")) Is Nothing Then Exit Sub
   
   If Range("G" & Target.Row) = 5 And Application.Max(Range("H" & Target.Row).Resize(, 3)) = 5 Then
     Range("K" & Target.Row).Interior.ColorIndex = 3
   ElseIf Range("G" & Target.Row) = 5 And Application.Max(Range("H" & Target.Row).Resize(, 3)) = 2 Then
      Range("K" & Target.Row).Interior.ColorIndex = 4
   Else
      Range("K" & Target.Row).Interior.ColorIndex = 48
   End If
End Sub

 

[MrSmith484]

You´re a champ! Thank you so much!!!!! !

 

[Fluff]

You're welcome