editing conditional formula

Question

I have a spreadsheet with a part of it G3:K8 that has conditional formatting with formulas. Since there is 1 letter e.g "L" in all the cells as part of the formula, is there some easy way to replace the letters each time I need to change them? The letters are random, so I had thought that some macro could accomplish this by searching for the letters in the formula and replace them with the new letters.

So, it might have the following "A" "B" "T" "R" "S" and I would like it to be "X" "X" "E" "T" "W". The quotes are part of the formula. Like a find/replace, but doesn't seem to be able to be done from the conditional

formatting box

Changing the formulas each time is quite tedious

thanks

Answer 1

Didn't work for me

I chose a word ALERT and you cough try BEACH after running code, and typing in alert or breach it should light in red

I will try for th 6th time to upload a pic with my formulas

The conditional formulas run from g3:k8 and are all repetetive...i just wanted to be able to pick out the letter by code and put in new letter

Answer 2

Okay - so you need to change the formula in the Conditional Formatting function, that is also doable.

Here's the code, which will delete all the existing formulas and will create again with the updated letter.

Since I don't know your entire formula, I just wrote one simple formula as an example, replace it with your actual formula on every Format Condition in the code, I also used the same colour just, for example, you can use different a color index based on your requirement, to see the list of numeric values of the colour index, check the screenshot and replace the numeric value in the code:

CAUTION: Please create the backup of your Excel file before executing this Macro code, just to be safer side.

Code:

Sub ReplaceFormula()
With Range("$G$3:$G$8")

.FormatConditions.Delete 'this will delete all the existing format conditions from the range G3:G8

'first condition
.FormatConditions.Add Type:=xlExpression, _
            Formula1:="=($G$4=""A"")" 'change letter here
        With .FormatConditions(1)
            .Interior.ColorIndex = 6 'change color here
            .StopIfTrue = False
        End With

'second condition
.FormatConditions.Add Type:=xlExpression, _
            Formula1:="=($G$4=""B"")" 'change letter here
        With .FormatConditions(2)
            .Interior.ColorIndex = 6 'change color here
            .StopIfTrue = False
        End With
        
'third condition
.FormatConditions.Add Type:=xlExpression, _
            Formula1:="=($G$4=""C"")" 'change letter here
        With .FormatConditions(3)
            .Interior.ColorIndex = 6 'change color here
            .StopIfTrue = False
        End With

'fourth condition
.FormatConditions.Add Type:=xlExpression, _
            Formula1:="=($G$4=""D"")" 'change letter here
        With .FormatConditions(4)
            .Interior.ColorIndex = 6 'change color here
            .StopIfTrue = False
        End With

'fifth condition
.FormatConditions.Add Type:=xlExpression, _
            Formula1:="=($G$4=""E"")" 'change letter here
        With .FormatConditions(5)
            .Interior.ColorIndex = 6 'change color here
            .StopIfTrue = False
        End With

End With
End Sub

Referenced file:

https://1drv.ms/x/s!AoD7X2fui8e1qjWNGt0AUe8gv1v...

Let me know in case of any further assistance is required, I will be glad to assist.

Answer 3

This is from the conditional formatting box with its my rules. The same letters appear from g3:g8

and are repeated h3:h8 to k3:k8 just different formatting. I was looking to be able to replace the letters I X C Q Y....with another set of letters, could be V D T C A

ThankS

Answer 4

Here's the basic example I used since I don't have the actual formula you're using.

Sub ReplaceFormula()
Dim strF As String
Dim strO As String
strF = Range("E4").Formula

'MsgBox (strF)

strO = Replace(strF, "L", "X")
strO = Replace(strO, "M", "Y")

'MsgBox (strO)

Range("E4").Formula = strO
End Sub

Explanation:

I am replacing formula in E4 cell,

Current E4 cell formula: =IF(D4>50,"L","M")

Now, using above VBA code, I am replacing "L" to "X" and "M" to "Y"

so after execution, the new formula in E4 cell will be =IF(D4>50,"X","Y")

Let me know in case of any further assistance is required, I will be glad to assist.

Answer 5

Hi AZ16,

Sorry to hear that you're experiencing this issue!

I am Bhavik, a Microsoft user like you. Please note, I am not a Microsoft employee and don’t have direct access to your account, but I will do everything I can to help you with your queries. :)

Can you provide a sample of a formula, so that I can help you with the correct code?

Usually, you would be able to do it by getting the cell formula in a string variable, performing search and replacing formula in that string variable and again assigning that string as the formula to a cell.

Thanks

Bhavik