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_Change(ByVal Target As Range)Dim r As Range If Not Intersect(Target, Range("B2:D2")) Is Nothing Then Set r = Range("D6:F6") If Target <> " " Then With r .Interior.ColorIndex = 3 End With End If End IfEnd Sub

Your post is a little confusing as to your exact requirements. When you talk about your cells being "filled in", do you mean with color, as it looks like that is what you are trying to do in your code? Or do you mean with a (any) value? Assuming that you want to color D6:F6 if there is any value in the range of B2:D2, and uncolor if there is no value in that range, then you could use the code posted.

If this doesn't meet your requirements, or you need changes, please post back.

And to take it a bit further, managing the range input, see below - and attached.

Cheers,

dave

Private Sub Worksheet_Change(ByVal Target As Range)Dim r As Range, fillit As Boolean If (Not Intersect(Target, Range("B2:D2")) Is Nothing) Or (Not Intersect(Target, Range("D6:F6")) Is Nothing) Then 'a change made in Range 1 or Range 2 fillit = False For Each r In Range("B2:D2") 'is there data in any of this range? If r <> "" Then fillit = True End If Next r If Not Intersect(Target, Range("B2:D2")) Is Nothing Then ' if change is being made in range 1 If fillit Then ' if so then color it Range("D6:F6").Interior.ColorIndex = 3 Else Range("D6:F6").Interior.ColorIndex = -4142 End If Else If Not Intersect(Target, Range("D6:F6")) Is Nothing Then 'making a change in Range 2 - check if that's ok If fillit Then 'there is something in Range 1 - so don't allow this change Application.EnableEvents = False 'so undo doesn't recurse Application.Undo Application.EnableEvents = True Else 'do nothing End If End If End If End IfEnd Sub

@Usk - you'll want to check each to see if you can copy/paste into the range, acting with > 1 cell change. Also, I like Sid's approach with global variable on previous value as opposed to my Application.Undo.

Mine also changes the fill color back to nothing, if Range 1 is cleared.

I've updated mine, to revert without the UNDO.

See below & attached,

Dave

Dim prevVals() As VariantPrivate Sub Worksheet_Change(ByVal Target As Range)Dim r As Range, fillit As Boolean If (Not Intersect(Target, Range("B2:D2")) Is Nothing) Or (Not Intersect(Target, Range("D6:F6")) Is Nothing) Then 'a change made in Range 1 or Range 2 fillit = False For Each r In Range("B2:D2") 'is there data in any of this range? If r <> "" Then fillit = True End If Next r If Not Intersect(Target, Range("B2:D2")) Is Nothing Then ' if change is being made in range 1 If fillit Then ' if so then color it Range("D6:F6").Interior.ColorIndex = 3 Else Range("D6:F6").Interior.ColorIndex = -4142 End If Else If Not Intersect(Target, Range("D6:F6")) Is Nothing Then 'making a change in Range 2 - check if that's ok If fillit Then 'there is something in Range 1 - so don't allow this change Application.EnableEvents = False 'so undo doesn't recurse Range("D6:F6") = prevVals Application.EnableEvents = True Else 'do nothing End If End If End If End IfEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) prevVals = Range("D6:F6")End Sub

1. Opened my worksheet.2. Saved the attachment "check-range-input-r2.xlsm" in Visual Basic for the worksheet. (Developer tab - Visual basic)3. Went back to the worksheet. When I enter data in Range("D6:F6") nothing occurs to Range("B2:D2").4. When I enter data in Range("B2:D2"), Range("D6:F6") fills in red and is locked and no data is allowed to be entered. This is good.5. But when I remove the data from Range("B2:D2") Range("D6:F6") does not remove the colors or the cell lock.

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

I'm sorry for not being clear but I am new at this and trying to learn.

I hope this clarifies what I am needing to accomplish.

I have two ranges for data entry:
Range1 = B2:D2
Range2 = D6:F6

If data is entered in any of the cells in Range1 then Range2 gets color filled and locked. If data is entered in any cell an error message appears stating that it is locked.
If data in all the cells of Range1 are cleared it unlocks and clears fill from Range2.

If data is entered in any of the cells in Range2 then Range1 gets color filled and locked. If data is entered in any cell an error message appears stating that it is locked.
If data in all the cells of Range2 are cleared it unlocks and clears fill from Range1.

SiddharthRout: Did you try the code that I or Zack posted? I tried your code and it did nothing. I do not know who Zack is.

firefytr: I loaded your code and received this error: ' Compile error: Method or data member not found'
Me.Range

Apologies for the confusion. I am Zack. My online moniker here is firefytr. Sorry about that.

This is a worksheet event, which means it must be housed in the worksheet code module of the worksheet you're working on. To do so, right click the worksheet name tab and select View Code. Paste the code into the module. You can only have one Option Explicit statement at the top of each module. You can only have one Worksheet_Change event per worksheet code module.

In the above post, I interrogeted to check whether there was a change in range 1 OR range 2. Then, if changes were being made in Range 1, if range 2 was empty, range 2 got colored Red, otherwise an error message popped up. Same thing if changes were being made in Range 2 instead of Range 1 - if Range 1 is empty, then Range 1 colored Red, otherwise an error message popped up. In either case, the VALID range was colored clear, while the INVALID RANGE (when the alternate range had data) was colored RED.

This allows changes anywhere else in the worksheet, except these two ranges - I assume that's your requirement, yes? Otherwise, I can do a simple modification.

firefytr: The code was probably functioning correctly but i could not figure out how to permanently remove the protection and cold not fully test it.

dlmille: Thanks for r4 I'll keep that in my library. r3 is great. Then only way i see to clear the background fill is to right click clear contents. Is there a way to also add only clear methods as space bar enter?

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…