# Using IF Function with Conditional Formatting

#### Shelby21

##### New Member
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 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
If E11 is totally empty, or has text, then it's not possible.

#### rigeljr

##### New Member
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
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

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

#### Shelby21

##### New Member
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

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
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
Glad you sorted it & thanks for the feedback.

#### Shelby21

##### New Member
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"

Replies
3
Views
115
Replies
4
Views
93
Replies
12
Views
108
Replies
4
Views
81
Replies
5
Views
112

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.

### Which adblocker are you using?

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

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