Tom,

Still shows as if white when using xlNone.

Not a big deal though, I can live with that. You have helped me out
greatly! Thanks again for your help!

Steve

"TomPl" wrote:

Try:

Target.Interior.ColorIndex = xlNone

Tom

"Steve" wrote:

Hey Tom,

The formula you gave me is working great! One question. I'm making a small
alteration and I was wondering what is the color index # for "no color"? I
was playing around with it and 00 is white, but i couldn't find anything that
is no color (google searches weren't helping much either). Let me know if
you have any ideas.

Thanks Tom!

Steve

"TomPl" wrote:

You could use language similar to my last response to set the color when you
set the validation.

Tom

"Steve" wrote:

Just thought of another possibility (probably a better way to do it) Is it
possible that if the cell includes a validation box that it is automatically
fill colored?

Steve

"TomPl" wrote:

In the "Else" part of you if statement you have "'do nothing".
Try replacing it with this:
If Target.HasFormula Then
With Target.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.InputMessage = ""
.ShowInput = True
.ShowError = True
End With
End If

I think that will work.

PS I don't like the use of cell iv1 as a variable. I would be inclined to
use a public VBA variable in its place.



"Steve" wrote:

I'm not sure if this is possible, but I hope it is. I'm fairly new to
working with VBA coding, but i'm sure someone is up to the challenge. Thanks!

I have this code which is all most 100% what I'm looking for. only problem
is I need what I hope will be a small adjustment/addition to it. Not sure
where it needs to go though. Please help.

Currently I have a code that when somebody goes in and overwrites a cell
with a formula in it, a box pops up asking for their name and reason for
overide. It then stores this information in a vaildation box.

What I still need:

What I need is a way to not have the vaildation box show up if a formula is
put back in the cell. The reason is because the same spreadsheet is used
each year. So every year there will be different cells that will be
overidden. Currently when you go to put a formula back in the cell it keeps
the validation box information from the change.

I'm thinking there is 2 ways this can be done, but I really don't know.

1. make an adjustment to the code where if a formula is put into the cell
the validation box disappears for that cell. (preferred way)

2. before putting a formula in the cell you must clear the cell of all
numbers and formulas - which will then make the validation box disappear.

Hope these ideas help. Your advice and help is greatly appreciated.

Here is the current code I have.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

Dim sReason1 As String
Dim sReason2 As String
Dim sUser As String
Dim dDate As Date
Dim sStatus As String

If ActiveSheet.Range("iv1").Value = 1 Then

Application.EnableEvents = False
sReason1 = InputBox("Enter Name (First, Last):")
sReason2 = InputBox("Enter the reason for the override:")
dDate = Date
sUser = Environ("username")
sStatus = "Date:" & dDate & " " & "Name:" & sReason1 & " " & "Reason:" &
sReason2
With Target.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = sUser
.ErrorTitle = ""
.InputMessage = sStatus
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Else
'do nothing
End If

Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.HasFormula Then
Application.EnableEvents = False
ActiveSheet.Range("iv1").Value = 1
Else
ActiveSheet.Range("iv1").Value = 0
End If

Application.EnableEvents = True

End Sub