PDA

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.