Changing cell colour as well as text depending on a range

[ladyath]

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.

 

[The Todal]

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]

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]

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]

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]

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.