Condiitional Formatting Key

[tobermory]

Bit of a long shot this... but...

I have a worksheet, called 'Key'. In column A there are around 12 cells inputted with text, each cell is coloured differently.

A1 has texted 'DS', and is yellow. A2 is 'Medical', and is red etc etc.

Would it be possible to somehow have a conditional format in Sheet2, whereby if any cell contains the text 'DS' (or any other text in Column A), it would refer back to worksheet 'Key' and return the colour of the cell in that worksheet?

At the moment I'm manually copy and pasting from the Key worksheet to Sheet2, to fill a work planner with the necessary colours. Sometimes the colours/words change which means I have to manually do it all again.

Many thanks to anyone who can help!

 

[Peter_SSs]

There isn't a direct way to do that if the Sheet1 cells are coloured manually. However, see if this work-around might be of use to you. I've done this with a small sample and only 3 colours but you could easily expand.

Instead of manually colouring the Sheet1 cells I have used a helper column (F) to indicate what colour I want the cells in column A to be using letters that relate to the colours (but could use full words or a number key if you want) and then used Conditional Formatting to apply those colours to column A

Excel Workbook
	A	B	C	D	E	F
1
2	DS					y
3	RR					r
4	XY
5	KY					b
6	LP					r
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =F2="b"	Abc
A2	2. / Formula is =F2="r"	Abc
A2	3. / Formula is =F2="y"	Abc

Now in Sheet2 I can use that same helper column to match the colours with this Conditional Formatting.

Excel Workbook
	C
1
2	LP
3
4	HH
5	KY
6	DS
7	KY
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =VLOOKUP(C2,Sheet1!A$2:F$6,6,0)="b"	Abc
C2	2. / Formula is =VLOOKUP(C2,Sheet1!A$2:F$6,6,0)="r"	Abc
C2	3. / Formula is =VLOOKUP(C2,Sheet1!A$2:F$6,6,0)="y"	Abc