DV or CF Formula?

[Pinaceous]

Good Day All,

I'm working with a database, between Columns E & F.

Column E has a numerical tally and Column F has an associated weight.

I'm looking to create a formula through DV or CF that when a numerical entry is made into Column E that its corresponding Column F if blank will highlight red with the words "Enter KG!".

Likewise, when a numerical entry made is made into Column F and Column E is blank that its cell highlights red with the words "Enter Tally".

If both cells of the corresponding Columns E & F are blank then nothing happens.

Not sure which would be the more stable action to take, either through DV or CF?

But I'd like to definitely stay away from the VBA approach.

Anyone can help? I'm all ears.

Thank you!
Pinaceous

 

[dreid1011]

Neither DV or CF can do what you want. You would need to use VBA. Is that an option?

 

[Pinaceous]

Neither DV or CF can do what you want. You would need to use VBA. Is that an option?

Good Day dreid1011,

What if I do not want the "Text" to appear in the opposite cell and to just highlight red?

Would I be able to work with DV or CF?

Thanks,
pinaceous

 

[dreid1011]

Yes, if you just wanted to highlight red, then a couple CF formulas would work. I also have some VBA written up if you want what you originally asked for.

 

[dreid1011]

Here are the CF rules:

Book1
	E	F
10	5
11	5
12	5
13		5
14		5
15		5
16	4
17	4
18		3
19		3
20	1
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F10:F20	Expression	=AND(F10="",E10<>"")	text	NO
E10:E20	Expression	=AND(E10="",F10<>"")	text	NO

Book1
	E	F
10	5	0
11	5
12	5
13		5
14	7	5
15		5
16	4	4
17	4
18		3
19		3
20	1
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F10:F20	Expression	=AND(F10="",E10<>"")	text	NO
E10:E20	Expression	=AND(E10="",F10<>"")	text	NO

And here is the VBA option:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fCell As Range, eCell As Range

If Not Intersect(Target, Range("E10:F20")) Is Nothing Then
    Application.EnableEvents = False
   
    If Target.Column = 5 Then Set fCell = Target.Offset(0, 1)
    If Target.Column = 6 Then Set eCell = Target.Offset(0, -1)
   
    Select Case Target.Column
        Case 5
            If fCell.Value = "" Then
                fCell.Value = "Enter KG!"
                fCell.Interior.ColorIndex = 3
            End If
            If Target.Value <> "Enter Tally" Then Target.Interior.ColorIndex = xlNone
        Case 6
            If eCell.Value = "" Then
                eCell.Value = "Enter Tally"
                eCell.Interior.ColorIndex = 3
            End If
            If Target.Value <> "Enter KG!" Then Target.Interior.ColorIndex = xlNone
        Case Else
            GoTo Breakout
    End Select
   
Breakout:
    Application.EnableEvents = True
End If
End Sub

 

[Pinaceous]

Thanks!

In using the CF approach, How would I access the menu "Cells with Conditional Formatting" as you have posted?

The first menu of CF appears, then the second menu appears below.

Please explain and thanks!

 

[dreid1011]

Select a range in column E, E10:E20 for example, then go to Conditional Formatting > New Rule. Select "Use a formula to determine which cells to format" and type in the following formula:
=AND(E10="",F10<>"")
Click Format and select the Fill tab and pick your color.

Repeat these steps with a range selected in column F, and enter the following formula:
=AND(F10="",E10<>"")

 

[Pinaceous]

Okay, copy and thank you!

 

[dreid1011]

Okay, copy and thank you!

You're welcome, and happy to help.