Changing cell colour as well as text depending on a range

ladyath

New Member
Joined
Aug 22, 2006
Messages
41
Apologies if this has been answered, but the search keeps timing out :(

I have a row that has lookup range values from 1-100 which needs to change colour as well as text depending on the numerical value.

Example: If the cell contains a value between 1-17, it must be green and display LOW instead of the number (eg 5). 18-39 would be yellow and must say MEDIUM, 40-63 must turn orange and read HIGH. 64-100 must change to red and read CRITICAL.

I can change the colour with conditional formatting (assuming I make the base colour green), but I can't seem to change the text. I am quite useless when it comes to VBA and not even sure where to start.

I'd appreciate it if someone could respond on how I can do this.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

The Todal

Board Regular
Joined
Jan 2, 2007
Messages
129
Can you have an adjacent or some other cell display LOW, MEDIUM, HIGH, CRITICAL? It would be fairly simple to do this and have both cells (or the whole row) change color.
 

ladyath

New Member
Joined
Aug 22, 2006
Messages
41
Unfortunately not. The cells that needs to change currently have a calculated value in a range as per my previous post. The spreadsheet is a dashboard with specific fields & rows, so I cannot just add additional rows - it would appear as duplication of the information.
 

ladyath

New Member
Joined
Aug 22, 2006
Messages
41
Can anyone help?
The columns affected are K and L.

I need the code to say something like:
If range($K:$L).value <0-17> then interior.colorindex = 4
If range($K:$L).value <18-39> then interior.colorindex = 6
If range($K:$L).value <40-63> then interior.colorindex = 46
If range($K:$L).value <64-100> then interior.colorindex = 3

If range($K:$L).value <0-17> then replace.value = "low"
If range($K:$L).value <18-39> then replace.value = "medium"
If range($K:$L).value <40-63> then replace.value = "high"
If range($K:$L).value <64-100> then replace.value = "critical"

And yes I am just guessing at what the code might be, but I hope it makes it a bit clearer on what I want to do. The colours I can do with conditional formatting, but would prefer to have it as code since I can have more than 3 conditions.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,116
Ladyath

You've got two problems here, and I think it's best to treat them separately.

First, if the cells already contain a formula, you should be able to edit the formula to get it to present "low" / "medium" etc instead of the number.

There are lots of ways to do this.
If your formula is already complicated, perhaps the easiest way is to turn it into a lookup formula.

Can you post the actual formula that is in the cell ?
 

ladyath

New Member
Joined
Aug 22, 2006
Messages
41
Currently the only formula in the cell is an averaged sum of a range of other cells from a different worksheet so I would prefer something I can put in the VBA for the worksheet.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,891
Messages
5,766,966
Members
425,391
Latest member
Alono23

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