Conditional formatting Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

scouse13

Thread Starter
Joined
Oct 29, 2009
Messages
137
as you can see from the attatched file, i have used conditional formatting to change the colour of a range of cells based on an entry made in cell E3,(with many thanks to ENT for his help), what i would like to do now, is be able to change the actual colour used based on an entry made in C3, the entry made in C3 would only change the colour used not alter the conitional format used for the entry made in E3, can this be done, or is it easier to put vba code within the sheet.
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
You have a number of color constants:
vbBlack 0x0
vbRed 0xFF
vbGreen 0xFF00
vbYellow 0xFFFF
vbBlue 0xFF0000
vbMagenta 0xFF00FF
vbCyan 0xFFFF00
vbWhite 0xFFFFFF

Try this site: www.cpearson.com/Excel/Colors.aspx
I got a lot of ideas here
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
BTW you mention range b10:b28 but what happens with b7:b:9 ????
Is tank 1, 3 and 8 always yellow? and tank 4, 17 and 22 always green?
I think vba will do it but I'll await your answer
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
Try this one:

I added a button, and I entered the values in column C, (now light grey, but you could make them white to be 'invisible'

The vba code uses this row to calculate which range is applicable, defines that as MYTANK and then depending on the tank number will color this one accrodingly.

Just add other tank nrs in the select statement and put your favourite color there and it's I think what you wanted.

P.S. I forgot to remove the conditional format in the tank range, you can do that since the code will supply the format for the selected area
 

Attachments

scouse13

Thread Starter
Joined
Oct 29, 2009
Messages
137
thanks keebellah thats more or less what i needed, i have used the dat validation tool to define what numbers can be entered in C, can the code be altered slightly so the show button is not required and it updates when the enter or tab keys are pressed.
also you said more tank numbers can be altered to the function statement, if required, i take it i could also add more case statements if further colours are required?

many thanks ( i have attatched the file again)
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
We will have to use the Worksheet_Change option so that everytime you change or the tank value or the value in E3 the range is updated, but won't it be a little messy?
That means if you change the tank the range changes and then again when you change the value in E3 it changes again.

But it's your sheet.

If you cant to adda more tanks with other colors then you have to add case statement vor each separate condition but that's simple now you have the code

I'll send you a asample when I to the on change option.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
Here's your example back again.

I editted so that when C3 or E3 are changed the tank changes
I removed your conditional format because this wouldn't allow other colors
I added the column with values under C again, only nog colored white font so they seem invisible.

You could also protect the sheet, so that only the tank range is editable and the 2 cells
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top