How to fulfill using VBA formating some range of Cells by certain rules?

[henryyh]

if I want to formating some cells by specific rules , how can I do to fulfill it ??

suppose: range("i2") represent some value , like 2.33
and I want some range of value , which < 2.33 , are formating become some color

such as：

if range("b2:F2") < range("i2")
then " match cells " .Interior.ColorIndex = 5

else if range("b2:f2") > range("i2") and range("b2:f2")< range("j2")
then " match cells " .Interior.ColorIndex = 3

else if range("b2:f2") > range("j2") and range("b2:f2") < range("k2")
then " match cells " .Interior.ColorIndex = 1

end if

 

[JackDanIce]

Welcome to the board. At a guess, your code might be:

Sub ConditionFormat()

Dim rng as Excel.Range

Application.ScreenUpdating = False

For Each rng in Range("B2:F2")
   With rng
   If .value < Range("I2").Value Then
      .Interior.ColorIndex = 5
   ElseIf .Value > Range("I2").Value And .Value < Range("J2").Value Then
      .Interior.ColorIndex = 3
   ElseIf .Value > Range("J2").Value and .Value < Range("K2").Value Then
      .Interior.ColorIndex = 1
   End If
   End With
Next rng

Application.ScreenUpdating = True

End Sub

 

[henryyh]

Welcome to the board. At a guess, your code might be:

Sub ConditionFormat()

Dim rng as Excel.Range

Application.ScreenUpdating = False

For Each rng in Range("B2:F2")
   With rng
   If .value < Range("I2").Value Then
      .Interior.ColorIndex = 5
   ElseIf .Value > Range("I2").Value And .Value < Range("J2").Value Then
      .Interior.ColorIndex = 3
   ElseIf .Value > Range("J2").Value and .Value < Range("K2").Value Then
      .Interior.ColorIndex = 1
   End If
   End With
Next rng

Application.ScreenUpdating = True

End Sub

I will try this code tomorrow , tks.

 

[henryyh]

Welcome to the board. At a guess, your code might be:

Sub ConditionFormat()

Dim rng as Excel.Range

Application.ScreenUpdating = False

For Each rng in Range("B2:F2")
   With rng
   If .value < Range("I2").Value Then
      .Interior.ColorIndex = 5
   ElseIf .Value > Range("I2").Value And .Value < Range("J2").Value Then
      .Interior.ColorIndex = 3
   ElseIf .Value > Range("J2").Value and .Value < Range("K2").Value Then
      .Interior.ColorIndex = 1
   End If
   End With
Next rng

Application.ScreenUpdating = True

End Sub

Thank you very mush , it's work perfectly , but if I want to put some loop within there , how can I fulfill it ?

1. Change constant number 2 to variable " i "
-> how could i do ?

case a:
For Each rng in Range("B(i):F(i)")
case b:
For Each rng in Range("B" &i : "F" & i )



such as:


Sub ConditionFormat()

Dim rng as Excel.Range

Application.ScreenUpdating = False


For i = 2 to 10

For Each rng in Range("B(i):F(i)")


With rng
If .value < Range("I(i)").Value Then
.Interior.ColorIndex = 5
ElseIf .Value > Range("I(i)").Value And .Value < Range("J(i)").Value Then
.Interior.ColorIndex = 3
ElseIf .Value > Range("J(i)").Value and .Value < Range("K(i)").Value Then
.Interior.ColorIndex = 1
End If
End With



Next rng
​

Next i

Application.ScreenUpdating = True

End Sub

 

[JackDanIce]

I think this is what you need:

Sub ConditionFormat()

Dim rng As Excel.Range
Dim i   As Long

Application.ScreenUpdating = False

For i = 2 To 10
    For Each rng In Range("B" & i).Resize(, 5)
        With rng
            If .Value < Range("I" & i).Value Then
                .Interior.ColorIndex = 5
            ElseIf .Value > Range("I" & i).Value And .Value < Range("J" & i).Value Then
                .Interior.ColorIndex = 3
            ElseIf .Value > Range("J" & i).Value And .Value < Range("K" & i).Value Then
                .Interior.ColorIndex = 1
            End If
        End With
    Next rng
Next i

Application.ScreenUpdating = True

End Sub

 

[henryyh]

your reply is so efficiently , I learn a lot from your code , thanks for you kindly helping , I will try this code tomorrow again.

 

[JackDanIce]

Thanks and good luck