Using IF Function with Conditional Formatting

[Shelby21]

Hi guys,

I am trying to create a conditional formula based on another cell's value.

If user inputs "No" into D11, I want E11 to change color to gray and put a dash in that cell.

I have tried writing standard IF function in conditional formatting using IF(D11="No","-"), but that just changed the cell to white with no dash.

I also tried using conditional formula D11="No" and also changing to custom number format by adding a dash as shown in Rules Manager below, but that only changes cell color to gray with no dash added.

I thought it was possible to do this with conditional formatting, but now I am not so sure.

If anyone knows how to do this with either conditional formatting or vba, it would be much appreciated!

 

[Fluff]

If E11 is totally empty, or has text, then it's not possible.

 

[rigeljr]

I'm not an expert here, but try adding an IF statement in E11 as well. I think the reason your dash doesn't show up is because the cell is empty.

Try entering =IF(D11="No",0,"") into E11

 

[Shelby21]

If E11 is totally empty, or has text, then it's not possible.

Hey Fluff,

If D11 input is "No" then E11 would be empty.

I thought this might be the case that it wasn't possible with conditional formatting, but I just couldn't remember.
I'll have to play around with it some more to see if I can find a work around for what I am looking for.

I'm thinking I can force the change using VBA, but I'm not sure how to do it that way so I'll have to do some research into that method.

 

[Fluff]

Can you put a formula in E11 as suggested by rigeljr?

 

[Shelby21]

I'm not an expert here, but try adding an IF statement in E11 as well. I think the reason your dash doesn't show up is because the cell is empty.

Try entering =IF(D11="No",0,"") into E11

I did try your method using conditional formatting, but the result was a blank white cell. Same as my first formula I tried.

I can't input this formula into E11 cell because that cell is for user input only, which is why I need to use either conditional formatting or vba to achieve what I am looking for.

Based on Fluff's response, sounds like conditional formatting is not possible so I am left with vba.

 

[Shelby21]

Can you put a formula in E11 as suggested by rigeljr?

No, because that cell needs to be reserved for user input if D11 = "Yes"

I did find a thread similar to what I am looking for:

VBA to update cell value based on another cell

I need to update the values in column A based on the value in column B. Here is the code: If Range("A2").Value = "refrin" Then Range("B2").Value = "RefrinDHP" Else End If If Range("A3").Value = "refrin" Then Range("B3").Value = "RefrinDHP" etc...

www.mrexcel.com

Sub Test()
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Range("A" & i).Value = "refrin" Then
            Range("B" & i).Value = "RefrinDHP"
        End If
    Next i
End Sub

I changed the above code to fit my criteria below:

Sub Test()
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("D" & Rows.Count).End(xlUp).Row
    For i = 11 To LastRow
        If Range("D" & i).value = "No" Then
            Range("E" & i).value = "-"
        End If
    Next i
End Sub

Nothing seems to change though when I tried this

 

[Shelby21]

Actually, I just got it to work this time.

I had other vba code in my sheet so I just included it in the same sub and it worked

Private Sub Worksheet_Change(ByVal Target As Range)
   
   If Not Intersect(Target, Range("D11:D20")) Is Nothing Then
      If Target(1).value = "yes" Then Target(1).value = "Yes"
      If Target(1).value = "YES" Then Target(1).value = "Yes"
      If Target(1).value = "no" Then Target(1).value = "No"
      If Target(1).value = "NO" Then Target(1).value = "No"
      If Target(1).value = "y" Then Target(1).value = "Yes"
      If Target(1).value = "Y" Then Target(1).value = "Yes"
      If Target(1).value = "n" Then Target(1).value = "No"
      If Target(1).value = "N" Then Target(1).value = "No"
   End If
   
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("D" & Rows.Count).End(xlUp).Row
    For i = 11 To LastRow
        If Range("D" & i).value = "No" Then
            Range("E" & i).value = "-"
        End If
    Next i
   
End Sub

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Shelby21]

I actually found a much easier way that worked better than my previous code.

Private Sub Worksheet_Change(ByVal Target As Range)
   
   If Not Intersect(Target, Range("D11:D20")) Is Nothing Then
      If Target(1).value = "No" Then Target(1, 2).value = "-"
      If Target(1).value = "Yes" Then Target(1, 2).value = ""
      If Target(1).value = "" Then Target(1, 2).value = ""

The cell color for column E is simply changed using conditional formula if column D = "No"