VB for macro needed urgent

sparq

New Member
Joined
Apr 3, 2011
Messages
3
here's my problem i need a code so that the text is on the adjacent cell not on the same cell, if the cell is color red the message next to it is "out of parameter", and another color is green, and the message next to it should be "ok" the messages should be on the next column(F2:F50).



Sub ParameterCheck()
Set colorcheck = Range("E2:E50")
For Each Cell In colorcheck

If Cell.Interior.ColorIndex = 3 Then
Cell.Value = "Out of Parameter"
If Cell.Interior.ColorIndex = 10 Then
Cell.Value = "Ok"

End If
End Sub


as soon as i run the macro, it displays the message on the cell itself. Help pls. Thanks in advance.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,846
Office Version
  1. 2019
Platform
  1. Windows
Welcome to the board!

Try

Code:
Sub ParameterCheck()
Set colorcheck = Range("B2:B50")
For Each Cell In colorcheck
If Cell.Interior.ColorIndex = 3 Then
Cell.Offset(, 1) = "Out of Parameter"
If Cell.Interior.ColorIndex = 10 Then
Cell.Offset(, 1) = "Ok"
End If
End If
Next
End Sub

Can also be done without VB if preferred.
 

sparq

New Member
Joined
Apr 3, 2011
Messages
3
how would be the code be if the its only used in a specific cell?
for example B2 would be the color then the color on C2?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,846
Office Version
  1. 2019
Platform
  1. Windows
Something like

Rich (BB code):
Sub ParameterCheck()
If Range("B2").Interior.ColorIndex = 3 Then Range("C2") = "Out of Parameter"
If Range("B2").Interior.ColorIndex = 10 Then Range("C2") = "Ok"
End Sub

Also just noticed a problem with the previous code, there was an End If in the wrong place, corrected below.

Rich (BB code):
Sub ParameterCheck()
Set colorcheck = Range("B2:B50")
For Each Cell In colorcheck
If Cell.Interior.ColorIndex = 3 Then
Cell.Offset(, 1) = "Out of Parameter"
End If
If Cell.Interior.ColorIndex = 10 Then
Cell.Offset(, 1) = "Ok"
End If
Next
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,048
Messages
5,526,472
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top