Hi everyone,
how to make only yellow cells mandatory?
The writing is horizontal, if I write in C3 and B3 is empty, a warning must appear etc ...
john
Hi everyone,
how to make only yellow cells mandatory?
The writing is horizontal, if I write in C3 and B3 is empty, a warning must appear etc ...
john
Sorry John
Your requirement is not clear. In your example B3 is not yellow so why would you expect a message?
Right Click on your sheet name at the bottom of excel and select view code.
Paste this code into the module that opens and close the module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Target.Interior.Color = 65535 And Target.Offset(0, -1) = "" Then MsgBox ("Please Enter Data in Cell: " & Target.Offset(0, -1).Address) End Sub
If you now select any yellow cell. You will get an alert if the cell to the left is empty.
This will get you started.
Last edited by mehmetcik; 09-18-2020 at 05:51 AM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
B3 is yellow for me? ( I suppose you meant C3 ?)
@ john
what are the rules to determine that B3 should be used before C4 ? What about E3 ?
Hi mehmetcik work almost good.
if I click in column A it gives an error
error defined by application or object
if B3 is not compiled it is not written in all other cells
if E3 is not compiled it is not written in all other cells
if C4 is not compiled it is not written in all other cells
if H4 is not compiled it is not written in all other cells
etc.....
Ahh column A will always give an error because we are checking the cell to the left.
Try this code instead.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 or target.column = 1 Then Exit Sub If Target.Interior.Color = 65535 And Target.Offset(0, -1) = "" Then MsgBox ("Please Enter Data in Cell: " & Target.Offset(0, -1).Address) End Sub
Hi mehmetcik work almost good.
Not work in column A
another mistake:
if I select a non-empty yellow cell, a warning appears
Last edited by john_cash; 09-18-2020 at 07:55 AM.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Or Target.Column = 1 Then Exit Sub If Target.Interior.Color = 65535 And Target.Offset(0, -1) = "" And Target.Value = "" Then MsgBox ("Inserisci valore nella cella: " & Target.Offset(0, 0).Address) End Sub
Hi mehmetcik
if I select a non-empty yellow cell, a warning appears <<< ok correct
Not work in column A
It cannot work in column A. There is no Cell to the left of Column A so there is nothing that must be filled.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo Quit If Target.Cells.Count > 1 Or Target.Column = 1 Then Exit Sub If Target.Interior.Color = 65535 And Target.Offset(0, -1) = "" And Target.Value = "" Then MsgBox ("Inserisci valore nella cella: " & Target.Offset(0, 0).Address) Quit: On Error GoTo 0 End Sub
Last edited by mehmetcik; 09-18-2020 at 09:40 AM.
Hi mehmetcik,
i inserted a support column A now works better.
Thank you very much
john
Hi
is a change possible?Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Or Target.Column = 1 Then Exit Sub If Target.Interior.Color = 65535 And Target.Offset(0, -1) = "" And Target.Value = "" Then MsgBox ("Inserisci valore nella cella: " & Target.Offset(0, 0).Address) End Sub
Now if I move with the tab closing the warning I can continue with the following cells.
Is this possible?:
until the first cell is filled in, you cannot continue and so on for the second, third etc ...
Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo Quit If Target.Cells.Count > 1 Or Target.Column = 1 Then Exit Sub If Target.Column = 2 Then Prev = Cells(Target.Row - 1, 11).Address Else Prev = Cells(Target.Row, Target.Column - 1).Address End If If Target.Interior.Color = 65535 And Range(Prev).Value = "" And Target.Value = "" Then MsgBox ("Inserisci valore nella cella: " & Prev): Range(Prev).Select Quit: On Error GoTo 0 End Sub
Hi mehmetcik it's ok.
it is possible instead of the yellow cells either
is referenced to cells but does not workIf Target.Interior.Color = 65535 And Range(Prev).Value = "" And Target.Value = "" _ Then MsgBox ("Inserisci valore nella cella: " & Prev): Range(Prev).Select
If Not Intersect(Target, Range("C3,F3,D4,I4,B6,C7,F7,I10,B12")) Is Nothing And Range(Prev).Value = "" _ And Target.Value = "" Then MsgBox "Inserisci valore nella cella: " & _ Target.Offset(0, 0).Address, vbInformation + vbOKOnly, "AVVISO!"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks