Conditional Formatting - Replacing cell value with Text

[Vergy39]

Is it possible to replace a cell value with text using conditional formatting. For example, a cell has a value of 94.75%. I would like to create a conditional format that will replace the value of 94.75% with the word "Green" if the value in that cell is Less than 101 and greater than 94.00. If the value is Greater than 101 and less then 94.00 then it would state the word "Red". is this possible? Any assistance is greatly appreciated.

Thanks
David V

 

[Isskint]

I can not see how this can be achieved. The only potential way i can imagine is to create your own icon set where Red and Green are images of the words - however the last time i checked, MS have not made this possible.

A simpler way to handle this would be an extra column using formulae.
EG in C2 if(b2=>0.94,if(b2<101,"Green","?"),"Red")




Also (it may be a typo ) but how can a value be Greater than 101 and less then 94.00?? :banghead:

 

[Vergy39]

I can not see how this can be achieved. The only potential way i can imagine is to create your own icon set where Red and Green are images of the words - however the last time i checked, MS have not made this possible.

A simpler way to handle this would be an extra column using formulae.
EG in C2 if(b2=>0.94,if(b2<101,"Green","?"),"Red")




Also (it may be a typo ) but how can a value be Greater than 101 and less then 94.00?? :banghead:

Thank you Isskinit for your response. I agree that it may not be possible as I have been all over the internet regarding this. I did see one page where they talked about using visual basic to accomplish this. Anyway, the cell value represents a percentage of staffing. We consider it good to be between 94 and 101 percent. Over 101 percent and less then 94 is not good.

Again, Thanks for you time.

David V

 

[Brianwarnock]

Thank you Isskinit for your response. I agree that it may not be possible as I have been all over the internet regarding this. I did see one page where they talked about using visual basic to accomplish this. Anyway, the cell value represents a percentage of staffing. We consider it good to be between 94 and 101 percent. Over 101 percent and less then 94 is not good.

Again, Thanks for you time.

David V

It is your system but I would have thought that it was better to show the figure and change the background colour.
Obviously it is >101 or <94 and I'm sure you realise that and would have coded it correctly


Brian

 

[Galaxiom]

It would be simpler to use a formula in an adjacent cell and hide the source value.

However, it is possible to change numbers into words using a custom cell format. The following format would display the strings in quotes.

"Pos";"Neg";"Zero";"Null"

The format has four sections separated by semicolons. The first is displayed when a value is positive, the second when it is negative, the third for zero and the fourth for Null values.

You could rescale the numbers to produce results in these categories.

 

[scott-atkinson]

The alternate option is to create a Conditional Format, to colour the background of each cell with the values on based on your criteria, this is a relatively simple process.

 

[Rx_]

If you want to use VBA with a Subroutine, the value can be evaluated and changed, or the background color changed, or what ever.
Look at commented 1755 -
This takes the value of one cell and assignes it to another cell in a hidden column.
You could just as easy put a condition thre that if this cell is > 100 then replace the same cell with some value.
This code has the OBJXL to run remotely from MS Access, so just remove that.
This Range object style code will run on Excel with 50,000 records very fast.
I have tried several methods over time. This is faster and easy to read.

1730        With ObjXL.ActiveWorkbook.ActiveSheet
1740            For i = intRowPos To intMaxRecordCount + intRowPos
1750             If .Cells(i, "B").Value <> .Cells(i - 1, "B").Value Then
1755                       '.Range(.Cells(i, "B"), .Cells(i, "H")).Font.FontStyle = "Bold"
                       '.Cells(i, 33).Value = .Cells(i, 3).Value
1760                   OtherColor = Not OtherColor ' this activates the every other group for greenbar
1770             Else
1780                  If OtherColor Then
                       '.Range(.Cells(i, "B"), .Cells(i, "C")).Font.ColorIndex = 16 'metalic gray to column B
1790                    .Range(.Cells(i, "B"), .Cells(i, "X")).Interior.ThemeColor = xlThemeColorAccent3 ' light green bar to column U
1800                    .Range(.Cells(i, "B"), .Cells(i, "X")).Interior.TintAndShade = 0.799981688894314
1810                    .Range(.Cells(i - 1, "B"), .Cells(i - 1, "X")).Interior.ThemeColor = xlThemeColorAccent3 ' light green bar to column U for first instance
1820                    .Range(.Cells(i - 1, "B"), .Cells(i - 1, "X")).Interior.TintAndShade = 0.799981688894314
1830                  Else
                        '.Range(.Cells(i, "B"), .Cells(i, "C")).Interior.ThemeColor = xlThemeColorAccent1
                        '.Range(.Cells(i, "B"), .Cells(i, "C")).Font.ColorIndex = 16 'metalic gray
1840                  End If
                 
1850             End If
1860           Next i
1870        End With