VBA to Create a speaker object

[hsandeep]

I have a contiguous rows of C2:C20 which generates +ve integers >0 and values in C2:C20 are different from each other. Similarly, C22:C23 which also generates +ve integers >0 and values in C22:C23 are different from each other.
For C2:C20, there are 2 cells $Y$4 which generates integers between 0 and 5 and $Z$4 which generates a +ve integer.
For C22:C23, there are 2 cells $Y$22 which generates integers between 0 and 5 and $Z$22 which generates a +ve integer.
$B$2 generates +ve integers >0 & keeps on changing its values.

In D2:D20, I want to fill either of ABOVE or BELOW based on the value of cell $B$2 divided by the value of cell $Z$4 (rounded to the nearest whole number) matching the value of any cell in the range C2:C20 and if match is found should fill ABOVE if $B$2>the matched C2:C20 else fill BELOW if $B$2<the matched C2:C20 else “” to be filled.

In the corresponding cell of the range E2:E20, I want to fill the corresponding C2:C20 if match is found else “” to be filled.

The code should Create a speaker object: object used to speak the text using the computer’s speech synthesis capabilities and

The ‘first speak’ should be D2:D20 containing either of ABOVE or BELOW followed by the corresponding cell value of the range E2:E20 provided both <>””. Please note here: after speaking, D2:E20 should be made as ClearContents. Moreover, this speech should be done in number of times=$Y$4 i.e if $Y$4=2, then 2 times it should speak.

All ‘subsequent speak’ should be D2:D20 containing either of ABOVE or BELOW followed by the corresponding cell value of the range E2:E20 provided both <>”” AND also if either of the corresponding D2:D20 OR the corresponding cell value of the range E2:E20 has changed. Please note here: after speaking, D2:E20 should be made as ClearContents. Moreover, this speech should be done in number of times=$Y$4 i.e if $Y$4=2, then 2 times it should speak.

In D22, I want to fill either of HIGHEST or “”based on the value of cell $B$2 divided by the value of cell $Z$22 (rounded to the nearest whole number) matching the value cell C22 and if match is found should fill E22 with C22 else “”.
In D23, I want to fill either of LOWEST or “”based on the value of cell $B$2 divided by the value of cell $Z$22 (rounded to the nearest whole number) matching the value cell C23 and if match is found should fill E23 with C23 else “”.

The ‘first speak’ should be D22 containing HIGHEST followed by the corresponding cell value E22 provided both <>””. Please note here: after speaking, D22:E23 should be made as ClearContents. Moreover, this speech should be done in number of times=$Y$22 i.e if $Y$4=2, then 2 times it should speak.

The ‘first speak’ should be D23 containing LOWEST followed by the corresponding cell value E23 provided both <>””. Please note here: after speaking, D22:E23 should be made as ClearContents. Moreover, this speech should be done in number of times=$Y$22 i.e if $Y$4=2, then 2 times it should speak.

All ‘subsequent speak’ should be D22 containing HIGHEST followed by the corresponding cell value of E22 provided both <>”” AND also if either of the corresponding D22 OR the corresponding cell value in E22 has changed. Please note here: after speaking, D22:E23 should be made as ClearContents. Moreover, this speech should be done in number of times=$Y$22.

All ‘subsequent speak’ should be D23 containing LOWEST followed by the corresponding cell value of E23 provided both <>”” AND also if either of the corresponding D23 OR the corresponding cell value in E23 has changed. Please note here: after speaking, D22:E23 should be made as ClearContents. Moreover, this speech should be done in number of times=$Y$22.

I have tried with below code (which is in complete) but could not achieve.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    If Me.Name <> "Sound" Then Exit Sub

    Dim currentTime As Date
    currentTime = Time
    If currentTime < TimeValue("09:30:00") Or currentTime > TimeValue("23:30:00") Then Exit Sub

    Dim value_B2 As Double
    Dim rounded_value As Double
    Dim speaker As Object
    Set speaker = CreateObject("SAPI.SpVoice")
    Static last_spoken_value As Double
    Dim cell As Range
    Dim next_cell As Range
    Dim valueChanged As Boolean
    Static last_D As Variant
    Static last_E As Variant
    Static lastCell As Range
    Static last_D_Value As Variant
    Static last_B2_Value As Variant
 
    If IsEmpty(last_D) Then last_D = "N/A"
    If IsEmpty(last_E) Then last_E = "N/A"
 
    If Target.Count = 1 Then
        If Target.Address = "$B$2" Then
            On Error Resume Next
            value_B2 = CDbl(Target.Value)
            On Error GoTo 0
         
            If value_B2 <> 0 And Me.Range("Z4").Value <> 0 Then
                rounded_value = Round(value_B2 / Me.Range("Z4").Value) * Me.Range("Z4").Value
             
                For Each cell In Me.Range("C2:C20")
                    If cell.Value = rounded_value Then
                        Me.Range("D2:D20").ClearContents
                        Me.Range("E2:E20").ClearContents
                     
                        If value_B2 < cell.Value Then
                            Me.Cells(cell.Row, 4).Value = "Below"
                        ElseIf value_B2 > cell.Value Then
                            Me.Cells(cell.Row, 4).Value = "Above"
                        End If
                        last_spoken_value = cell.Value
                     
                        Me.Cells(cell.Row, 5).Value = cell.Value
                     
                        If Not lastCell Is Nothing Then
                            If lastCell.Address = cell.Address And (IsEmpty(last_D_Value) Or last_D_Value <> Me.Cells(cell.Row, 4).Value) Or last_E <> Me.Cells(cell.Row, 5).Value Then
                                Set lastCell = cell
                                last_D_Value = Me.Cells(cell.Row, 4).Value
                                last_B2_Value = value_B2
                                last_E = Me.Cells(cell.Row, 5).Value
                            End If
                        End If
                     
                        If last_D_Value <> last_D Then
                            last_D = Me.Cells(cell.Row, 4).Value
                            last_E = Me.Cells(cell.Row, 5).Value
                            speaker.Speak CStr(last_D) & " " & CStr(last_E)
                        End If
                        Exit For
                    End If
                Next cell
            End If
        ElseIf Target.Column = 4 Then
            If Target.Value <> last_D Then
                last_D = Me.Cells(Target.Row, 4).Value
                last_E = Me.Cells(Target.Row, 5).Value
             
                speaker.Speak CStr(last_D) & " " & CStr(last_E)
            End If
        End If
    End If

    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

How to accomplish?
Would be glad to answer any number of questions.