Need Help in VBA

Hi everyone!

I have the requirement of changing the font color of one range of values depending on another range of values. Below is my code. But it is not working effectively. Can anyone help me what is wrong with it?

Thanks a lot in advance :)

l

Code:

---

row = Range("C6").End(xlToRight).Column  /This is to know the last column
Set rng = Range("C7:" & Cells(15, lrow).Address(RowAbsolute:=False, columnabsolute:=False)) /The range starts from C7
Set rng1 = Range("C37:" & Cells(45, lrow).Address(RowAbsolute:=False, columnabsolute:=False)) /Ends at 37th row but the column is whatever "lrow" has
Workbooks(sheet.Range("C4").Value).Activate
Sheets("Sheet1").Activate
For Each Item In rng1
If Item < 30 Then
rng1(Item).Font.Color = RGB(255, 0, 0)
rng(Item).Font.Color = RGB(255, 0, 0)
End If
Next Item

---

The requirement is if any value < 30 in one range then this value & the other range corresponding value's font color should be changed to red.
Hope it is clear.

Quote:

---

Originally Posted by LalithaG

Hi everyone!

I have the requirement of changing the font color of one range of values depending on another range of values. Below is my code. But it is not working effectively. Can anyone help me what is wrong with it?

Thanks a lot in advance :)

lrow = Range("C6").End(xlToRight).Column /This is to know the last column
Set rng = Range("C7:" & Cells(15, lrow).Address(RowAbsolute:=False, columnabsolute:=False)) /The range starts from C7
Set rng1 = Range("C37:" & Cells(45, lrow).Address(RowAbsolute:=False, columnabsolute:=False)) /Ends at 37th row but the column is whatever "lrow" has
Workbooks(sheet.Range("C4").Value).Activate
Sheets("Sheet1").Activate
For Each Item In rng1
If Item < 30 Then
rng1(Item).Font.Color = RGB(255, 0, 0)
rng(Item).Font.Color = RGB(255, 0, 0)
End If
Next Item

The requirement is if any value < 30 in one range then this value & the other range corresponding value's font color should be changed to red.
Hope it is clear.

---

I solved it finally with the help of senior :D

instead of "item", I have created a range variable "oCell" and used it in place of item. So the code will be as below :

Code:

---

Dim oCell as Range

lrow = Range("C6").End(xlToRight).Column /This is to know the last column
Set rng = Range("C7:" & Cells(15, lrow).Address(RowAbsolute:=False, columnabsolute:=False))
Set rng1 = Range("C37:" & Cells(45, lrow).Address(RowAbsolute:=False, columnabsolute:=False))

Workbooks(sheet.Range("C4").Value).Activate
Sheets("Sheet1").Activate
For Each oCell In rng1
If oCell.value < 30 Then
rng1(Item).Font.Color = RGB(255, 0, 0)
rng(Item).Font.Color = RGB(255, 0, 0)
End If
Next oCell

---

Thank you all for looking into it :)

Could you be so kind and at the top go to FAQ, How do I post a question and How do I post code samples.
It is probably a given that you are here because you "Need Help in VBA". That does not explain your problem in a meaningful "Google" way.

Ok. I will do that :)

Quote:

---

Originally Posted by LalithaG

Ok. I will do that :)

---

Thanks for posting the solution

While you're reading the FAQs ...

1. You can mark you thread as [SOLVED] by going to the [Thread Tools] pull-down above your first post and clicking [Mark Solved] (we try to keep things tidy :))

2. You can put CODE tags around macros to set the code off and do some formatting to make it easier for others. Use the [#] on the post command bar to insert the tags, and then just paste the macro between them

I did that for your first posts for you so you can see how it high lights your macro