Thread: Auto Number with header row and Formatting

Issue Number 1: I have a task list that I am trying to set up an auto number VBA code for. I have a header row in my work sheet and i would like that to be excluded from the count or auto number. I have already figured out how to do auto date and time entry but I am struggling with auto number. Can someone please help? I have pasted the code I currently have below as well as a listing of the column headers for reference.

Row 1 - Headings
Column A - ID# (Auto)
Column B - Date (Auto)
Column C - Time (Auto)
Column D - Issue (Manual Entry)
Column E - Responsible Party (Manual Entry)
Column F - Actions Taken (Manual Entry)
Column G - Status (Dropdown List)
Column H - Cost (Manual Entry)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

        If Not Intersect(Target, Range("D2:D1000")) Is Nothing Then

            With Target(1, -1)

                .Value = Date

                .EntireColumn.AutoFit

            End With
            With Target(1, 0)

                .Value = Time

                .EntireColumn.AutoFit

            End With
            
        End If
        
        End Sub

Issue Number 2: I currently have the Status column set up with conditional formatting so that the 3 different options appear as different colors (Open=Red, In Progress=Yellow and Closed=Black). This however only changes the color the single cell of column G. I would like to have it color the ENTIRE row and change when the value in column G changes. Is this possible? If so how?

Thanks in advance

First part:

        Target.Offset(0, -3) = Target.Offset(-1, -3) + 1

Second part:

use: Formula is: =$G2="Open" ... etc as the condition on all the cells. Select cell A2 to whatever before you make the change or you will get unusual results.

Regards

Have a look at the updated example file.

Regards

I suspect that there is a non numeric ID in the previous row. Even a space would cause it.

Regards