Conditional format cells

steve263

New Member
Joined
Nov 13, 2005
Messages
20
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. :confused:
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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() :oops:
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,791
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
you could use the OR function

select formula is

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

and then choose your required format
 

steve263

New Member
Joined
Nov 13, 2005
Messages
20
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,791
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,493
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 13, 2005
Messages
20

ADVERTISEMENT

test board
weekly check board.xls
ABCDEFG
1SunMonTueWedThurFriSat
2DO093009300930DO09300930
3K010aW209A203WA203WL235SD6SD6
4SD6SD6A204K215WA205SD6SD6
5DOA203WK203TMTA203WA203WA503
6DO0600R0430K201WR0430DILSD6
7DOL281L281L281L251DOL563
8DILL340W238WL336DOR1631DIL
9DOIIIIIIIIIIDO
10A002L210R1100R1100L218L218DO
11DOW218INTK207A201WA201WA501
12DOA204W202L270W212W214A502
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,493
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 13, 2005
Messages
20
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,493
Office Version
  1. 365
Platform
  1. Windows
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).
 

Forum statistics

Threads
1,136,991
Messages
5,678,996
Members
419,797
Latest member
ikethegenius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top