View Full Version : COLOURED CELLS
mahadeshwar
05-18-2006, 03:04 AM
HI everybody need your help again
does anybody know how to do the following
I have cells in one worksheet all different colours
red, blue, yellow etc
and IN THE CORRESPONDING CELLS IN COLUMN G i want to print high, med, low etc
eg
IF CELL F2 IS RED THEN G2 - HIGH
IF CELL F3 IS BLUE THEN G2 = MED
MANY THANKS
TonyJollans
05-18-2006, 04:43 AM
As with your other question, you need VBA to test cell colours - see if you can adapt jonkske's code to give you what you want.
lenze
05-18-2006, 02:21 PM
How are the cells colored? If by conditional formatting, you can use the same condition in an IF statement in column G. If not you will need code
lenze
lenze
05-18-2006, 02:34 PM
Here is some code
Sub HighlightMe()
Dim cl As Range
Dim rng As Range
Set rng = ActiveSheet.Range("$F2:F" & Range("$F65536").End(xlUp).Row)
For Each cl In rng
Select Case cl.Interior.ColorIndex
Case Is = 3
cl.Offset(0, 1).Value = "HIGH"
Case Is = 5
cl.Offset(0, 1).Value = "MED"
Case Is = 6
cl.Offset(0, 1).Value = "LOW"
'etc
Case Else
End Select
Next cl
End Sub
This assumes data is in column F. If not, you will need to set your range
set rng = ActiveSheet.Range("F2:F100")
mahadeshwar
05-18-2006, 03:44 PM
ITS BRILLIANT CODE THANKYOU
but for some dum reason all my colou idex values are set to 6 so it keeps printing out lowcan you hellp i hav this problem
TONY DID THIS BRILLIANT CODE
=IF(OR(AND(D2<=3,E2=1),AND(D2<=2,E2=2)),"VLOW","")
BUT I HAVE THESE TWO CONDITIONS THA T I NEED TO NEST INTO THE IF STATEMENT SO THAT IT DOES ALLL THE COMPARISON OR ONE VALUE IN THE CELL AND PRINTS THE WORD BUT I DON?T KNOW HOW
SEE CONDITIONS BELOW
IF(OR(AND(D2>=3,E2=3),AND(D2<=2,E2=4)),?MEDIUM?),
IF(OR(AND(D2>=3,E2=4),E2=5),?HIGH?)
lenze
05-18-2006, 06:15 PM
Repeating myself, but HOW are your cells colored? It sounds like they are colored Yellow(ColorIndex = 6) and are being changed by Conditional Formatting, which the code will not pick up. If that is true, you can use the same conditions for your IF statment. Can you attach a sample file?
lenze
TonyJollans
05-18-2006, 11:27 PM
mahadeshwar,
1. Please stop SHOUTING
2. Do you still have an issue here? I've lost track of the different threads.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.