troubleshooting Question

Fill cell if empty

Avatar of usky1
usky1 asked on
Microsoft Excel
26 Comments2 Solutions496 ViewsLast Modified:
I am new to Excel coding and was trying to figure a way to fill in cells.

Using Excel 2007

Example: I have 2 ranges set.
Range 1 = cells B2, C2 and D2
Range 2 = cells D6, E6 and F6

All cells are blank.
If I enter anything in either of the cells in Range 1 then all the cells in Range 2 are filled in and as long as anything stays in Range 1 nothing can be entered in Range 2.
I then need an error message to pop up stating that you can only enter information for one range.

I tried this as a start to fill in if not blank and that is why I am asking for help.

Private Sub Worksheet_Blank(ByVal Target As Range)
Dim r As Range
If Target.Address <> "$B$2:$D$2" Then Exit Sub
Set r = Range("D6:F6")

If Target <> " " Then
With r
.Interior.ColorIndex = 3
End With
End If

End Sub

Zack Barresse
Excel Dev

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 26 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 26 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros