Proper Singular Macro Code to Highlight/Unhighlight a Cell in Another Sheet

You have really done your homework. It will be a pleasure helping you. Start with understanding event procedures. They save you the trouble of clicking buttons. Therefore you don't need buttons - or much fewer, at least - if you understand Excel's application events.

The design I will suggest to you replaces your existing procedures in the Bid sheets with this one.

Option Explicit

Private Sub WorkSheet_Change(ByVal Target As Range)
    ' 095
    ApplyTimeStamp Target
End Sub

Observe the "Option Explicit" instruction at the top. You should have this in every code module. It's the sure-fire remedy against pulled or premature gray hair.The single line of code calls a procedure and passes the Target (the clicked cell) to that procedure. The above procedure is the same in all sheets because the procedure called is the same for all sheets which is possible because all your bid sheets enjoy an identical structure. From the point of view of the called procedure every call is different because Target is different.

The procedure responding all is the one below. It must be in a standard code module. Excel doesn't have a standard code module by default. You will need to insert it. Its default name will be Module1 but I strongly urge you to give it a meaningful name. I usually name them, like, TXL_4712 where the number is the thread number here (no spaces permitted) but you may prfer a name like "EventManager". Keep code that doesn't match the description in another module.

Option Explicit

' identify columns on the Summary tab
Public Const AlertClm       As String = "B"     ' column to highlight
Public Const AcknClm        As String = "Q"     ' "Acknowledge" column

Sub ApplyTimeStamp(Target As Range)
    ' 095

    Dim TriggerRng      As Range        ' action only if change occurred here
    Dim BidId           As String       ' Bid Package ID extracted from STab name
    Dim Rt              As Long         ' Target column (in Summary sheet)

    With Target
        With .Worksheet
            ' action only if change occurred here
            Set TriggerRng = .Range(.Cells(10, "B"), .Cells(14, "B"))
            If Not Application.Intersect(Target, TriggerRng) Is Nothing Then
                Application.EnableEvents = False
                .Cells(3, "B").Value = Format(Now(), "mm-dd-yyyy hh:mm AM/PM")
                BidId = Trim(Split(.Name)(0))
            End If
        End With
    End With

    If Len(BidId) Then                  ' blank if no time stamp was applied
        With Worksheets("Summary Sheet")
            On Error Resume Next
            Rt = WorksheetFunction.Match(BidId, .Columns(AlertClm), 0)
            If Rt Then                  ' do nothing if a match wasn't found
                .Cells(Rt, AlertClm).Interior.Color = vbYellow
                .Cells(Rt, AcknClm).Value = "Acknowledge"
            End If
            Err.Clear
            Application.EnableEvents = True
        End With
    End If
End Sub

There are two constants at the top of the code which you need to set. They are used throughout the project. Therefore they are Public. (Set them once and for all, not everywhere interspersed into the code.) Normally, I would use column numbers but for now letters will do. In your Summary Sheet the ''Acknowledgement' column actually is column P. I used the next column because P is occupied. You can change the column later. No need to look into the code. One change here will do the job.

The code first sets the time stamp. Avoid defining target cells as offsets from the Target. Observe that Target.Offset(-6) isn't the same as Cells(Target.Row, "B"). That's because Target.Row depends upon the cell where the change occurred. Your code will place the time stamp all over the place, always 6 rows up from where you changed something. What if your change was in Row 42?

In its second part the code highlights the corresponding row on the summary sheet.. It would but take a minor chage to really highlight the entire row because the MATCH function already found its number. However, for now the code just highlights the reference in the BidID column. And it writes the word "Acknowledge" to the Acknowledgement column, in deference to your button of the same name.

As you see, the Alert buttons have become obsolete. To do the same to the Acknowledge buttons please install the event procedure below in the code sheet of your Summary tab.

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' 095

    Dim TriggerRng      As Range            ' the range where double-click triggers action

    Set TriggerRng = Range(Cells(6, AlertClm), _
                           Cells(Rows.Count, AlertClm).End(xlUp)) _
                          .Offset(0, Columns(AcknClm).Column - Columns(AlertClm).Column)
    If Not Application.Intersect(Target, TriggerRng) Is Nothing Then
        With Target
            If .Value = "Acknowledge" Then
                .Value = ""
                With Cells(.Row, AlertClm)
                    .Interior.Pattern = xlNone
                    .Select
                End With
                Cancel = True
            End If
        End With
    End If
End Sub

Observe that this code uses the same constants that were declared in the standard code module. This event procedure responds to the double-click of a cell (not the change). Just double-click on the word "Acknowledge" in column Q and the word disappears along with the highlight. 

Here is an overview of the workflow.

1. Make a change in B10:B14 in any of the bid sheets.
   1. The time stamp will be applied to B3 of that sheet
2. The corresponding cell in the Summary sheet (column B) will be lit and the word "Acknowledge" added in column Q.
   1. The macro identifies the row using the first word of the sheet name.
   2. You can add/delete sheets and items at will.
3. Double-click on the word "Acknowledge" to delete the word and the highlight.