Conditional format cells

[steve263]

Hi,
I wish to C.F. cells in a worksheet with the same colour for different cell values. I.E. I have a worksheet with columns A to G that are for Sun to Sat. I then have rows A1 to 50. Within the worksheet there are, on different rows in different cells, names such as SK or II or HLA and DIL and others.

I want to colour SK and II the same colour and HLA and perhaps DIL another colour. I can format them one at a time, but as you are only allowed three different C.F.'s in a workbook, I have quickly run out of options. As there are quite a few names throughout the workbook all standing for different reasons, this is getting messy. Is there a way to CF cells together with different names?

At the moment I am using the formula in CF 'cell value is equal to ="SK" then I format it the way I like. I would like to add II to the format and make the two the same, as they are basically similar formats in my workbook.

Does this post make any sense? I can post a example, but when I tried the other day, the workbook was to large, and the format colours had been taken out.

 

[Jon von der Heyden]

Hi

You can set conditions using formula:

In Condition1 change from "Cell Value is" to "Formula is":
e.g. =OR(A1="SK",A1="II")
then apply desired format.

You can apply this method accross the board.

I've used A1 as an example, but you need to make sure that you select the activecell of the range that you want to apply his to.

Hop this makes sense.

Jon

Edit: Made mistake in intitial formula, should be OR()

 

[jimrward]

you could use the OR function

select formula is

=OR(A1="AK",A1="IL")

and then choose your required format

 

[steve263]

I used the formula as follows, =OR($A$2:$G$34="SK",$A$2:$G$34="II") and all I got was the same colour throughout the work sheet. Where it should have picked up the SK and II as the formatted colour, I got a worksheet full of one colour.

 

[jimrward]

you need to select the first cell and use as mentioned earlier

=OR(A2="SK",A2="II")

then use the paintbrush tool on the toolbar, double click on this and select the remainder of the cells you wish to apply the formatting to. then clcik on the brush to release the control

 

[Peter_SSs]

I can post a example, but when I tried the other day, the workbook was to large, and the format colours had been taken out

1. 'Workbook was too large.' Just choose a smaller section - we should still be able to get the idea from this smaller sample.

2. 'Format colours had been taken out.' Yes, Conditional Formatting does not transfer to the board with Colo's HTML Maker. However, you can add manual colour as well as the conditionally formatted colour to the relevant cells before using the HTML Maker. That way, we will see the colour on the board. (Don't forget to then remove the manual colour from those cells)

 

[steve263]

test board

weekly check board.xls
	A	B	C	D	E	F	G
1	Sun	Mon	Tue	Wed	Thur	Fri	Sat
2	DO	0930	0930	0930	DO	0930	0930
3	K010a	W209	A203W	A203W	L235	SD6	SD6
4	SD6	SD6	A204	K215W	A205	SD6	SD6
5	DO	A203W	K203	TMT	A203W	A203W	A503
6	DO	0600	R0430	K201W	R0430	DIL	SD6
7	DO	L281	L281	L281	L251	DO	L563
8	DIL	L340	W238W	L336	DO	R1631	DIL
9	DO	II	II	II	II	II	DO
10	A002	L210	R1100	R1100	L218	L218	DO
11	DO	W218	INT	K207	A201W	A201W	A501
12	DO	A204	W202	L270	W212	W214	A502
Sheet1

The above is trial board. The colours are not important I just need to be able to format the same letters 'ie' do' in different colours. I have tried the ways suggested, but although it works for the first time, when adding other colours for different letters, the poor thing throws a fit. Please note that there are a lot of different letters than the ones I have shown.

 

[Peter_SSs]

steve263

Glad to see that you have the HTML Maker working now. Not sure that I fully understand what you want but if you want more than 3 colours (depending on cell contents) then you will not be able to use standard Conditional Formatting. In this case you would need some VBA code.

1. Can you confirm that you do actually want more than 3 colours?
2. Are there any different combinations for the same colour (eg 'SK' and 'II' as mentioned in an earlier post)? Can't see any example sof this in your last post.
3. In your sample A2 ('DO') is shaded blue but other 'DO' cells like A9 and G10 are not blue. Is this what you want? If so, how is it decided which ones are blue and which ones are not?

 

[steve263]

Colours and cells

I did not do the cell colouring very well I think. I would probably want more than three colours. For instance SK and II and AST would be the same, then DIL and HLA and SD6 would be another, then DO another one. There may be others, but that is all I can think of right now. As for VBA, there we strike out. I have attempted very simple macros, but any more and I am a lost soul.

 

[Peter_SSs]

Re: Colours and cells

.. I would probably want more than three colours. ... As for VBA, there we strike out. ..

This is a critical decision. If you want more than 3 colours (as well as leaving some cells white) then this cannot be done without VBA or without the upcoming Excel12 (Excel 2007).