Using IF Function with Conditional Formatting

Shelby21

New Member
Joined
Nov 21, 2017
Messages
29
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!


excel.PNG
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
If E11 is totally empty, or has text, then it's not possible.
 

rigeljr

New Member
Joined
Jul 5, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2017
Messages
29
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Shelby21

New Member
Joined
Nov 21, 2017
Messages
29
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

New Member
Joined
Nov 21, 2017
Messages
29

ADVERTISEMENT

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 Code:
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:

VBA Code:
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

New Member
Joined
Nov 21, 2017
Messages
29
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 :)

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Shelby21

New Member
Joined
Nov 21, 2017
Messages
29
I actually found a much easier way that worked better than my previous code.

VBA 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"

snip.PNG
 
Solution

Forum statistics

Threads
1,140,932
Messages
5,703,234
Members
421,285
Latest member
Bebek

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top