condition formatting based on cell value

[mwvirk]

Step 1: if i have a value in cell A1 then cell B1 C1 D1 E1 should change the color yellow
Step 2: but once i have a value in F1 then cell B1 C1 D1 E1 should change the color green

Now I don't know the formula for condition formatting but I feel that how 2 different colors will change on 2 different valid conditions.
If cell A1 has a value then it will do the job but what will happen if cell F1 also has a value then which condition will work?
Maybe it will overwrite the 1st (A1) if 2nd (F1) is true?
If not then maybe I need to use the condition for A1 to check if there is a value then do step 1 to change the color to yellow and then use the condition for F1 to check if there is a value in A1 and F1 to change the color to green

Honestly, I don't know if this is valid logic and someone might have a better idea/solution. also, even though I am sharing the logic but I don't know how to code it in Excel condition formatting as a formula.

Please help with this.

Thanks

 

[Scott Huish]

Select Columns B:E
Click on Conditional Formatting, then New Rule
Check Use a Formula to determine which cells to Format
=$A1<>""
Format as Yellow fill
Click OK

Click on Conditional Formatting, then New Rule
Check Use a Formula to determine which cells to Format
=$F1<>""
Format as Green fill
Click OK

 

[mwvirk]

Select Columns B:E
Click on Conditional Formatting, then New Rule
Check Use a Formula to determine which cells to Format
=$A1<>""
Format as Yellow fill
Click OK

Click on Conditional Formatting, then New Rule
Check Use a Formula to determine which cells to Format
=$F1<>""
Format as Green fill
Click OK

nice and simple. perfect.
just a small issue here...
if cell F1 is having value but cell A1 is blank how can force user to add value in cell A1 first before adding value in F1

right now i tried removing the value from A1 and cell are remain green.

 

[Scott Huish]

For the first problem, change the 2nd formula to =AND($F1<>"",$A1<>"")

 

[mwvirk]

For the first problem, change the 2nd formula to =AND($F1<>"",$A1<>"")

yes. it's working.
if i want to issue a pop-up then data validation can be done here? what will be the settings for data validation?

 

[Scott Huish]

So, if there is a value in A1 and F1 already are you going to allow the removal of a value in A1 or not? And if so, what do you want to have happen in that circumstance?

 

[mwvirk]

So, if there is a value in A1 and F1 already are you going to allow the removal of a value in A1 or not?

no.
first, there must be a value in A1 then only F1 should accept the value.
now for any reason, a user adds a value in A1 then F1. After that value of A1 should not be deleted unless the user deletes the value from F1 first then delete A1

 

[Scott Huish]

Try this. If you try and put a value in column F and there is no value in Column A, it will alert you to that fact and delete the entry.
Now I've not used Application.Undo that much, but in the second instance, if you try and blank the value in Column A and there is a value in Column F, it will alert you to that fact and revert the change.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Range("F:F"), Target)
If Not d Is Nothing Then
    Application.EnableEvents = False
        For Each c In d
            If Range("A" & c.Row) = "" Then
                MsgBox "Cell " & Range("A" & c.Row).Address(0, 0) & " must contain a value"
                c.ClearContents
            End If
        Next
    Application.EnableEvents = True
End If

Set d = Intersect(Range("A:A"), Target)
If d Is Nothing Then Exit Sub
    Application.EnableEvents = False
        For Each c In d
            If c = "" And Range("F" & c.Row) <> "" Then
                MsgBox "Cell " & c.Address(0, 0) & " must contain a value"
                Application.Undo
            End If
        Next
    Application.EnableEvents = True
End Sub

 

[mwvirk]

thanks a lot Scott Huish. it solved the problem.
really appreciate your help.