# Changing cell colour as well as text depending on a range

##### New Member
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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### The Todal

##### Board Regular
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.

##### New Member
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.

##### New Member
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

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 ?

##### New Member
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.

Replies
7
Views
101
Replies
1
Views
423
Replies
5
Views
101
Replies
10
Views
302
Replies
6
Views
182

1,186,371
Messages
5,957,469
Members
438,307
Latest member
bigmike1720

### 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.

### Which adblocker are you using?

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

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