Conditional Formatting based on another cells

[sg2209]

hi Friends ,

Regarding conditional formatting i have checked couple of examples and created the below code however its not working not sure why , Could anyone please review and share where i am making a mistake , also if need to apply this only in the specific sheet names Raw , how do i append it .

Dim k As Long, r1 As Range, r2 As Range
For k = 2 To 5
Set r1 = Range("C18" & k)
Set r2 = Range("B29" & k & ":C33" & k)
If r1.Value >= 500 Then r2.Interior.Color = vbGreen
If r1.Value > 1000 Then r2.Interior.Color = vbYellow
If r1.Value > 2000 Then r2.Interior.Color = vbRed
If r1.Value > 5000 Then r2.Interior.Color = vbYellow
If r1.Value >= 5000 Then r2.Interior.Color = vbYellow






Next k

 

[AlphaFrog]

You could use actual Conditional Formatting instead of vba code.

First select cells B29:C33 and enter these CF forualas in this order...

Condition 1
Formula is: =$C18>=5000 'Background color Yellow

Condition 2
Formula is: =$C18>=2000 'Background color Red

Condition 3
Formula is: =$C18>=1000 'Background color Yellow

Condition 4
Formula is: =$C18>=500 'Background color Green


Excel will automatically change the formula cell reference $C18 for each row.

 

[sg2209]

Thank you So much however i am using this in a Form which is getting prepared through VBA , so i need to use this in that .

 

[sg2209]

and i need to change to colour for cells B29:C33

 

[AlphaFrog]

You could have VBA set the actual Conditional Formatting formulas.

Your code is ambiguous. Can you better explain the criteria, conditions and data ranges?

 

[sg2209]

below is the condition when i used in Excel , however i am not able to convert this in VBA even i did try it with teh above Code

=And($C18>=1,$C18<=500) then Cell B29:C33 Should be Yellow
=And($C18>=501,$C18<=1000) then Cell B29:C33 Should be Yellow
=And($C18>=1001,$C18<=2000) then Cell B29:C33 Should be Yellow
=And($C18>=2001,$C18<=5000) then Cell B29:C33 Should be Yellow
=And($C18>5000) then Cell B29:C33 Should be Yellow

 

[sg2209]

i have tried with below code its not working too , keeps on Highlighting B29
If Range("C18") <= "500" Then
Range("B29:C29").Interior.Color = vbYellow
End If


With Sheets("INS REFUND CHECK REQ").Range("B29:C33").Select
Range("B29:C33").Interior.Pattern = xlNone
End With


If Range("C18") < "501" And Range("C18") >= "1000" Then
Range("B30:C30").Interior.Color = vbGreen
End If




With Sheets("INS REFUND CHECK REQ").Range("B29:C33").Select
Range("B29:C33").Interior.Pattern = xlNone
End With


If Range("C18") >= "1001" And Range("C18") <= "2000" Then
Range("B31:C31").Interior.Color = vbYellow
End If


With Sheets("INS REFUND CHECK REQ").Range("B29:C33").Select
Range("B29:C33").Interior.Pattern = xlNone
End With


If Range("C18") >= "2001" And Range("C18") <= "5000" Then
Range("B32:C32").Interior.Color = vbYellow
End If




With Sheets("INS REFUND CHECK REQ").Range("B29:C33").Select
Range("B29:C33").Interior.Pattern = xlNone
End With
If Range("C18") > "5000" Then
Range("B33:C33").Interior.Color = vbYellow
End If

 

[sg2209]

Hi All ,

the above code works well when i add $C18,

thank you all for the efforts