If Statement W/ Colour

[Jose Miguel]

Hello All,

Quick question. I have the following.

Real One to Look at for PHSA.xls
	A	B	C	D	E	F
227	226	24270-225				CONTNR COOLER LABTOP .32 PLACE
228	227	24270-226				COOLER UNIT NALGENE 12-TUBE
229	228	24710-102	89000-252			CYL GRAD CLASS B CALBRTD "TO DELIVER" 50ML
230	229	24710-124	89000-254			CYL GRADUATED PYREX TC 100ML
231	230	24710-240	KT481755-0100			CYL GRADUATED
232	231	24710-249	KT481755-1000			CYL GRADUATED SNGL METRIC SCALE
233	232	24710-340				CYL GRADUATED PYREX 250ML
234	233	24774-785				CYL GRAD PMP 2L
Replacement and Discontinued

I want to try to achieve the following:

If C is green, then "x" in D1
If C is yellow then "C1" in E1
If C is blank then do nothing

I can't wrap my brain around creating If statements using colour backgrounds as conditions. Could someone kindly help? Thank you.

Jose

 

[Joe Was]

Conditional Formatting or VBA, not with Sheet Formulas though!

 

[Jose Miguel]

Hello Joe Was,

I'll look into the VBA code if all else fails, but how would I specify the colour of the text as a condition, and what would be the outcome format? Thanks.

Jose

 

[Joe Was]

If your interior cell colors are the lightest Yellow and the lightest green from the cell color utility pallet, then this code will work from any code module. To make it run automatically change this Sub to a Change Event!

This code will do what you want for every cell in column C.

To use conditional formatting you will find getting the right formula a problem, for color you will be limited to detevting Font color and not Cell coloe as: [Red] to indicate the font color and I seldom use Conditional Formatting, it is too limited and the formula code uses a different syntax that is difficult to work out more offten than not. I use VBA code:


Sub colorTest()
Dim myBot&, myRng As Range

myBot = Range("C65536").End(xlUp).Row

Set myRng = Range(Cells(1, 3), Cells(myBot, 3))

For Each c In myRng
If c.Interior.ColorIndex = 35 Then c.Offset(0, 1).Value = "x"
If c.Interior.ColorIndex = 36 Then c.Offset(0, 1).Value = c.Offset(0, 2).Value
Next c

End Sub

 

[simsy]

Perhaps another way of looking at this might be better...

How are the cloured cells being coloured?

If you are using conditional formatting it should be easy to use the same conditions to provide the results you want...

If you are currently not using conditional formatting, and are colouring the cells manually... then perhaps you should use conditional formatting...

You could, for example, enter x in D1 and use this a condition to turn C1 Green...

Would this approach work? If so we can refine it to get what you want.

Regards,

Simsy