# Posts by lenze

Re: Formula: Input in anyone of three cells solves for other two.

So, you DON'T really need to place a formula in any of the cells. You just want to calculate and populate the other 2 when 1 of them is changed. Is that correct??. The code above does that, but we can't help you unless we know the formulas.

lenze

Re: Formula: Input in anyone of three cells solves for other two.

Post YOUR formulas, although I 'm not really sure of what you need.
lenze

Re: Formula: Input in anyone of three cells solves for other two.

lenze

Re: Auto Sort

Use a WorkSheet Change Event on H4:H16 and place the sort code in it. You can get the syntax by recording a macro while preforming a Sort
lenze

Like this??

Code
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row <> 3 Then Exit Sub
If UCase(Target) = "X" Then
Cells(4, Target.Column) = Date & " " & Time & " by " & Environ("UserName")
End If
End Sub``````

That said why not use a comment?

Code
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row <> 3 Then Exit Sub
If UCase(Target) = "X" Then
Target.AddComment.Text Text:="Revised " & Format(Date, "mm-dd-yyyy") & " at " & Time & Chr(10) & "By " & Environ("UserName")
End If
End Sub``````

lenze

Re: Log Worksheet Changes

Another approach that I find useful is to use a comment to track changes

lenze

Re: Counting Number Of Instances Of A Value

Just use a Pivot Table. Put Date in the Row filed, Zip in the row field and Zip in the in the Data Field. Make sure the Data field is set to Count

lenze

Re: Don't Overwrite Cells

Don't know if it will work with a PT, but try

Code
``Application.DisableAlerts = False``

lenze

Re: Changing The Font Color Of Formulas

Perhaps

Code
``````Private Sub Worksheet_Activate()
Cells.SpecialCells(xlCellTypeFormulas).Font.ColorIndex = 3
End Sub``````

lenze

Re: If And Nested Functions

You can shorten your formula to
=IF(OR(N10={1,2,3,4,5,}),0,G10)
lenze

Aaron: I really like your nested approach. I just never thought of it.
I also thought he could use Cells

Code
``Cells(ActiveCell.Row-3,1).Resize(7,1).....``

lenze

You may need to combine Offset with Resize

Code
``Range("C10").Offset(-3,0).Resize(7,1).Select``

Are you sure you really need to select? You usually can work with ranges without selecting them.

Code
``Range("C10").Offset(-3,0).Resize(7,1).Copy Sheet2.Range("\$A\$1")``

This will copy C7:C13 To Sheet2 A1.
Edit: Yu can use ActiveCell instead of a range such as C10

Code
``ActiveCell.Offset(-3,0).Resize(7,1).Copy Sheet2.Range("\$A\$1")``

lenze

Re: Lock Cells Passed Current Date

Where did you place the code/ it should be in the ThisWorkBook module. Right click the Excel icon left of "File" on the menu bar and choose "View Code"

Also, if row 1 is a header row, change the line

Code
``For Each cl In Sheet1.Range("\$A\$1:\$A" & Range("\$A\$65536").End(xlUp).Row)``

to

Code
``For Each cl In Sheet1.Range("\$A\$[b]2[/b]:\$A" & Range("\$A\$65536").End(xlUp).Row)``

lenze

Re: Lock Cells By Current Date

Assuming Date in Column A

Code
``````Private Sub Workbook_Open()
Dim cl As Range
For Each cl In Sheet1.Range("\$A\$1:\$A" & Range("\$A\$65536").End(xlUp).Row)
If cl < Date - 1 Then Range(Cells(cl.Row, 1), Cells(cl.Row, 21)).Locked = True
Next cl
End Sub``````

lenze

Re: Changing Font Color Depending On Adjacent Cell Color

Code
``````Sub ChangeFont()
Dim cl as Range
For Each cl in Range("\$B\$2:\$B" & Range("\$B\$65536").End(xlUp).Row)
cl.Font.ColorIndex = cl.Offset(0,-1).Interior.ColorIndex
Next cl
End Sub``````

lenze

Re: Popup Message Before Saving With Conditions

Since your sample does not contain code, I'm guessing Dave used a BeforeSave Event.

Code
``````Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheet1.Range("D7") = "RET" And Sheet1.Range("D15") = 0 Then
MsgBox "Whatever"
Cancel = True
End If
End Sub``````

lenze

Re: Unprotect And Reprotect The Sheet After Update Cells

Is your copying done via Code or manually?

lenze

Re: Adding A Value To A Value

Something like this?

Code
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "\$C\$1" Then Exit Sub
Range("\$E\$1") = Range("\$E\$1") & " " & Target.Text
End Sub``````

Assumming your dropdown is in \$C\$1 and your cell to append is in \$E\$1

lenze

Re: Double Click In Cell To Show Current Date And Time

Perhaps

Code
``````Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("\$C\$2:\$E\$400")) Is Nothing Then Exit Sub
Target = Now
Cancel = True
End Sub``````

But this may be of interest
http://vbaexpress.com/kb/getarticle.php?kb_id=909