Add character to cell based on confitional formatting fill

Hi Guys,

Once again I turn to smarter brains for help!

Please see example attached.

I dont even know if VBA is capable of identifying conditionally formatted colours.

I need a macro that will loop through the cells in range E5:Z1110, identify the colour of the conditional formatting, then depending on the colour, add a 'colour code' to the cell.

The colours are as follows:

Grey: R-128, G-128, B-128
Red: R-255, G-124, B-128
Amber: R-255, G-255, B-102
Green: R-196, G-215, B-155

The character to insert will be as follows:

Grey: N
Red: R
Amber: A
Green: G

The other part would be that the current date would have to be converted to text in the "dd/mm/yy" format when the character is inserted.

For example

green cell containing "Jun 12" -----> G22/06/12
Grey cell containing "N/A" ------> NNA
Red Cell cell containing "Jul 11" -----> R24/07/11
Red cell containing "Overdue" ------> ROverdue

Is this possible?

Looking forward to an answer

F

Thank you Patel,

I have reformatted this to use the following colours:

Grey - [Color 48] #969696
Red - [Color 22] #FF8080
Amber - [Color 27] #FFFF00
Green - [Color 43] #99CC00

I have attached a new example sheet using the new colours in the conditional formatting.

Many thanks fo rany help you can give

F

Hi Patel,

The colour of the cell (cell.Interior.ColorIndex) is 4142 for all cells since the fill colour is applied through conditional formatting. I think that's why it will not work this way because it identifies all cells as 4142 (blank) and so it does not add any characters.

Any other ideas?

Thank you for your time.

F

patel45, If you want to check the color displayed by the conditional formatting, you need to check which of the formula of the formatting returns True. The task is not easy as it seemed at first. Good luck.

Artik

Hi Patel, Hi Artik,

I managed to work around this with a non VBA solution, essentially i created a new page with the same format, then created a formula which evaluated all of the conditional formatting rules to return the colour code.

Please Login or Register  to view this content.

Thank you for your help with this! I didnt know VBA was incapable of identifying conditionally formatted colours.

Thanks Again for you time!

F