Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Change value in cell with conditional formatting?

0

Hello,
We are trying to add a text from B4 if in G4 has the value 1
But we can't manage to change the 1 to OK
Can someone helpme please

see here
How we can do that.
We using 365 in windows excel

Thank you

Answer
Discuss

Answers

0
Selected Answer

Hello again Ghost,

Conditional formatting can't add text to a cell.

> Updated 10/13 @10am; plus added a sample file

You can't use a traditional "IF" because it has 2 outcomes - True and False. In CF rules, the formula must only equate to True.
However, you can apply conditional formatting to G4  in a couple of ways. You apply it based on B4 value by the use formula method   =$B$4 = "OK". Or based on the value in G4 - cell value contains. This would require using an in-cell formula in G4    =IF(B4="OK", "OK", 1).  This method restricts G4 to being "OK" or 1 (or whatever you wish).

An easy way to check the formula result for the use formula method is to enter it ( B4 = "OK" ) into a worksheet cell and see if it returns the result you need (True or False)

You can change the value in G4 using a Worksheet_Change event (VBA method for those who want to use VBA):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$B$4" Then Exit Sub

    If Target.Value = "OK" Then
        Range("G4").Value = "OK"
        Else: Range("G4").Value = 1
    End If

End Sub

In the attahed file there are 2 methods of cell contains and 1 for use formula method.

Hope this helps, if so, please mark my answer as Select

Cheers   :-)

Discuss

Discussion

Hi WillieD24, Thanks for helping me out. i'm aware of the vba code in excel But the problem is we can't use vba in de browser version of 365 excel So is there any other way for using it without vba? Thanks :)
GhostofWanted (rep: 52) Oct 13, '24 at 1:59 am
Hello Ghost,

See my updated answer. I have attached a file with non-VBA methods and the Worksheet_Change mothod. I also left the VBA method in my answer for those viewers who may want to use VBA.

Cheers   :-)
WillieD24 (rep: 657) Oct 13, '24 at 10:25 am
Hello willieD24, Amazing, i think i can do something with it Thanks for helping me out 😊
GhostofWanted (rep: 52) Oct 13, '24 at 11:42 pm
Glad I was able to help. Thanks for selecting my answer.

Cheers   :-)
WillieD24 (rep: 657) Oct 14, '24 at 12:34 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login