Applying colour change to a row before a yes/no question on whether this is the right row is answered

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,244
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have some code that asks if this row is the correct row and I want it somehow made visually different when I ask the question so you know which row the question is relating to. This is the code I have but the row only turns a different colour after the question is asked. How do I get it to change colour or something similar after the question is asked but before it is answered?

VBA Code:
                                Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).row).SpecialCells(xlCellTypeVisible).Count
                                'if this range is greater than 1, ask the below question, else continue
                                If rws > 1 Then
                                'If ws.Range("A4", ws.Range("A4").End(xlDown)) > 1 Then
                                    Dim Answer As Integer
                                    Dim RowNumber As Long
                                    Dim RowLine As Range
                                    For Each RowLine In rng
                                        RowLine.EntireRow.Interior.ColorIndex = 3
                                        Answer = MsgBox("Is this the job you want to apply the late cancel price too?", vbQuestion + vbYesNo + vbDefaultButton2, "Late Cancel Price")
                                        If Answer = vbYes Then
                                            'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them
                                            RowNumber = RowLine.row - 3
                                            'RowLine.EntireRow.Interior.ColorIndex = 3
                                            GoTo FoundRightJob
                                        End If
                                        'If answer = vbNo
                                       
                                    Next RowLine
                                End If

Thanks
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
Have you gotr Screen updating turned off??
This code works for me (A modifciation of my previous answer to you:)
VBA Code:
Sub test()
Dim ws As Worksheet
Set ws = Worksheets("sheet1")

Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
Dim rng As Range
Set rng = ws.Range("A4:A" & LastRow)
Dim row As Range
Dim answer As Integer
  For Each row In rng
      row.EntireRow.Interior.ColorIndex = 3
      answer = MsgBox("Is this the job you want to apply the late cancel price too?", vbQuestion + vbYesNo + vbDefaultButton2, "Late Cancel Price")
      row.EntireRow.Interior.ColorIndex = 0
        If answer = vbYes Then
         rowno = row.row
         GoTo Foundrightjob
        'If answer = vbNo
        End If
  Next row
Foundrightjob:
Cells(rowno, 2) = "This is the row selected"
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,244
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
If you turn off screen updating , does that mean that even if you tell the code to update the screen, such as changing the colour, nothing will happen?

I won't be able to check until I get to work tomorrow but I think screen updating is turned off when the code runs.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,205
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Dave screen updating turned off simply means the user doesn't see any activity on screen, thereby saving time on the running of the macro.
everything coded will still heppen, but you won't see it until screenupdating is returned to TRUE
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

If you turn off screen updating , does that mean that even if you tell the code to update the screen, such as changing the colour, nothing will happen?

I won't be able to check until I get to work tomorrow but I think screen updating is turned off when the code runs.
Correct, you will see nothing!!
I very rarely turn screen updating off, because if you write really fast macros you don't need to turn screen updating off. I use frequently use variant arrays to speed up a macros, which is much more effective than turning screen updaing off.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,244
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
The code in post 2 doesn't do everything I need it to do. The question must only be asked if there is more than 1 row filtered.


This is my code
VBA Code:
                                Dim LastRow As Long
                                LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                                 Dim rng As Range: Set rng = ws.Range("A4:A" & LastRow)
                                
                                Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count
                                'if this range is greater than 1, ask the below question, else continue
                                If rws > 1 Then
                                'If ws.Range("A4", ws.Range("A4").End(xlDown)) > 1 Then
                                    Dim answer As Integer
                                    Dim RowNumber As Long
                                    Dim RowLine As Range
                                    Application.ScreenUpdating = True
                                    For Each RowLine In rng
                                        RowLine.EntireRow.Interior.ColorIndex = 3
                                        answer = MsgBox("Is this the job you want to apply the late cancel price too?", vbQuestion + vbYesNo + vbDefaultButton2, "Late Cancel Price")
                                        RowLine.EntireRow.Interior.ColorIndex = 0
                                        If answer = vbYes Then
                                            'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them
                                            RowNumber = RowLine.Row - 3
                                            GoTo FoundRightJob
                                        End If
                                        'If answer = vbNo
                                        
                                    Next RowLine
                                End If
FoundRightJob:

There are 3 entries with the same request number and date. There is a totals sheet that has 2 fields for the date and request number and there is a button to run the code on the sheet. If I run this code by pressing the button on the totals sheet, after the date and request number have been entered:
  1. The totals sheet remains the active sheet
  2. The Late Cancel Price message box will appear
  3. If I press No, the message box will disappear and reappear with the same question
  4. If I press Yes, The msg box disappears and I check the relevant monthly sheet of the date that was entered and the second date, going from top to bottom has the late cancel pricing applied to it.
  5. The code appears to apply the pricing to the correct date, ie, if I press Yes on the third message box instance, the third duplicate will have the pricing applied to it.

What I need after the button has been pressed to execute the code:
  1. The active sheet to become the sheet that contains the relevant jobs that have the date and request number
  2. The first entry that the question is being asked about to become highlighted so the user knows which entry the question is relating too
  3. If I press No, the message box will disappear and reappear with the same question. The row that was highlighted will become not highlighted and the next row the question is now relating too will now become highlighted
  4. If I press Yes, same as above but I won't need to switch to the monthly sheet as I will already be there and I will see the change take place in real time
  5. The correct row that had Yes selected for it then will need to become not highlighted.

I hope that makes sense. I have included the whole procedure in case you need it for some reason.

VBA Code:
Sub LateCancel()
        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, QT As String, wb2 As Workbook, WbPath As String, QTPath As String
        Dim Serv As String, Month As String, Service As String, LCPrice As String, AutoFilterCounter As Long
        Set wb2 = ThisWorkbook
        'QT = "CSS_quoting_tool_29.5.xlsm"
        Set sh = wb2.Worksheets("Totals")

        'values on totals sheet that the user is looking for
        Dim LCReq As String: LCReq = sh.Cells(32, 2).Value
        'Dim LCDt As String: LCDt = sh.Cells(37, 2).Value
        Dim LCDt As String: LCDt = CDate(sh.Cells(37, 2).Value)
        Dim LateCancelHours As String: LateCancelHours = sh.Cells(35, 2).Value
        Dim SheetCounter As Long: SheetCounter = 0
        
        WbPath = ThisWorkbook.Path
        QTPath = ThisWorkbook.Path & "\..\" & "\..\"
Call TurnOffFunctionality
        'If Not isFileOpen(DocYearName & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Work Allocation Sheets" & "\" & Site & "\" & DocYearName & ".xlsm"
        'If Not isFileOpen(QT) Then Workbooks.Open QTPath & "\" & QT
    
        For Each ws In wb2.Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" And ws.Name <> "Sheet2" Then
                        With ws.[A3].CurrentRegion
                                'On Error Resume Next
                                'Autofilter the late cancel date enter in B37 with dates in column 1
                                .AutoFilter 1, LCDt
                                'Autofilter the late cancel request number with request numbers in column 3
                                .AutoFilter 3, LCReq
                                
                                'Check to see if the date cell, column A, for a job has anything in it. If it doesn't, turn the autofilter off and skip to the next sheet.
                                If ws.[A3].Cells.Offset(1, 0) = "" Then
                                    .AutoFilter
                                    SheetCounter = SheetCounter + 1
                                    'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know
                                    If SheetCounter = 12 Then
                                        MsgBox "A job with the date and request number entered does not exist"
                                    End If
                                    GoTo SkipNextSheet
                                End If
                                
                                'Check the count Autofilter
                                AutoFilterCounter = .Columns(1).SpecialCells(xlCellTypeVisible).Count
                                'If value less than 2, only the heading is visible so skip to the next sheet.
                                If AutoFilterCounter < 2 Then
                                    .AutoFilter
                                    'Add 1 to a sheet counter
                                    SheetCounter = SheetCounter + 1
                                    'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know
                                    If SheetCounter = 12 Then
                                        MsgBox "A job with the date and request number entered does not exist"
                                    End If
                                    GoTo SkipNextSheet
                                End If
                                
                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        'Check if there is a service entered in column 5 of the filtered job.
                                        If .Areas(1).Cells(1, 5).Value = "" Then
                                            'Display a messagebox with a message and the sheet that has the missing service.
                                            MsgBox "There is a job in the " & ws.Name & " sheet that matches the date and request number but does not have a " & _
                                            "service type. Please add a service type to this job before continuing."
                                            Call TurnOnFunctionality
                                            .AutoFilter
                                            'Cells(32, 2).ClearContents
                                            'Cells(37, 2).ClearContents
                                            Exit Sub
                                        End If
                                        'If the service column, (5), has a value, store the service in the service variable.
                                        Service = .Areas(1).Cells(1, 5).Value
                                    End With
                                
                                
                                Dim LastRow As Long
                                LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                                 Dim rng As Range: Set rng = ws.Range("A4:A" & LastRow)
                                
                                Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count
                                'if this range is greater than 1, ask the below question, else continue
                                If rws > 1 Then
                                'If ws.Range("A4", ws.Range("A4").End(xlDown)) > 1 Then
                                    Dim answer As Integer
                                    Dim RowNumber As Long
                                    Dim RowLine As Range
                                    Application.ScreenUpdating = True
                                    For Each RowLine In rng
                                        RowLine.EntireRow.Interior.ColorIndex = 3
                                        answer = MsgBox("Is this the job you want to apply the late cancel price too?", vbQuestion + vbYesNo + vbDefaultButton2, "Late Cancel Price")
                                        RowLine.EntireRow.Interior.ColorIndex = 0
                                        If answer = vbYes Then
                                            'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them
                                            RowNumber = RowLine.Row - 3
                                            GoTo FoundRightJob
                                        End If
                                        'If answer = vbNo
                                        
                                    Next RowLine
                                End If
FoundRightJob:
                                    
                                    'Copy data fom the job back to a calculator on the data sheet (this is the code name for sheet2) to calulcate the price again.
                                    With Data
                                        .Cells(30, 1) = CDate(LCDt)
                                        '.Cells(30, 1) = Format(Date, "d/mm/yyyy")
                                        '.Cells(30, 1).NumberFormat = "d/mm/yyyy"
                                        .Cells(30, 2) = Service
                                        'Set the hourly figure in the lateCanel table to be the LateCancelHours variable
                                        .Cells(30, 5) = LateCancelHours
                                        'A late cancel will be charged for 1 staff member attending
                                        'Therefore, set the Staff Req. figure to 1
                                        .Cells(30, 6) = 1
                                    End With
                                    On Error GoTo Price
                                        'Calculates price of late cancel on worksheet so the new price will be copied to the allocation sheet instead of the previous price
                                        Calculate
                                    LCPrice = Data.Cells(30, 8).Value
Price:
                                Select Case Err.Number
                                    Case Is = 13
                                        MsgBox "There is a problem with the spelling of the service type on the " & ws.Name & " sheet for the job that matches " _
                                        & "the date and request number. Please check the spelling and try again."
                                        'Cells(32, 2).ClearContents
                                        'Cells(37, 2).ClearContents
                                        .AutoFilter
                                        Call TurnOnFunctionality
                                        Exit Sub
                                End Select
                                On Error GoTo 0
                                With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                    Dim LTCnclDate As String
                                    .Areas(1).Cells(RowNumber, 1).Value = "LT CNCL " & .Areas(1).Cells(1, 1).Value
                                    .Areas(1).Cells(RowNumber, 8).Value = LCPrice
                                    .Areas(1).Cells(RowNumber, 9).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                                    .Areas(1).Cells(RowNumber, 10).Formula = "=RC[-1]+RC[-2]"
                                End With
                                
                              
                                .AutoFilter
                        End With
                End If

SkipNextSheet:
        Next ws
'sh.Range("B32,B37").ClearContents
Call TurnOnFunctionality

End Sub


Thanks guys
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,244
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I worked it out. I just needed to add this line of code at the start of the for each loop
VBA Code:
ws.activate

Now my code looks like this
VBA Code:
                                Dim LastRow As Long
                                LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                                Dim rng As Range: Set rng = ws.Range("A4:A" & LastRow)
                                
                                Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count
                                'if this range is greater than 1, ask the below question, else continue
                                If rws > 1 Then
                                'If ws.Range("A4", ws.Range("A4").End(xlDown)) > 1 Then
                                    Dim answer As Integer
                                    Dim RowNumber As Long
                                    Dim RowLine As Range
                                    Application.ScreenUpdating = True
                                    For Each RowLine In rng
                                        ws.Activate
                                        RowLine.EntireRow.Interior.ColorIndex = 6
                                        answer = MsgBox("Is this the job you want to apply the late cancel price too?", vbQuestion + vbYesNo + vbDefaultButton2, "Late Cancel Price")
                                        RowLine.EntireRow.Interior.ColorIndex = 0
                                        If answer = vbYes Then
                                            'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them
                                            RowNumber = RowLine.Row - 3
                                            GoTo FoundRightJob
                                        End If
                                        'If answer = vbNo
                                        
                                    Next RowLine
                                End If
FoundRightJob:


I love being able to understand vba more so I can sometimes debug myself!
 
Solution

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,244
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Found another problem. I now have 4 duplicate rows with the same date and request number plus a heap of other random dates.

This is now my whole sub
VBA Code:
Sub LateCancel()
        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, QT As String, wb2 As Workbook, WbPath As String, QTPath As String
        Dim Serv As String, Month As String, Service As String, LCPrice As String, AutoFilterCounter As Long
        Set wb2 = ThisWorkbook
        'QT = "CSS_quoting_tool_29.5.xlsm"
        Set sh = wb2.Worksheets("Totals")

        'values on totals sheet that the user is looking for
        Dim LCReq As String: LCReq = sh.Cells(32, 2).Value
        'Dim LCDt As String: LCDt = sh.Cells(37, 2).Value
        Dim LCDt As String: LCDt = CDate(sh.Cells(37, 2).Value)
        Dim LateCancelHours As String: LateCancelHours = sh.Cells(35, 2).Value
        Dim SheetCounter As Long: SheetCounter = 0
       
        WbPath = ThisWorkbook.Path
        QTPath = ThisWorkbook.Path & "\..\" & "\..\"
Call TurnOffFunctionality
        'If Not isFileOpen(DocYearName & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Work Allocation Sheets" & "\" & Site & "\" & DocYearName & ".xlsm"
        'If Not isFileOpen(QT) Then Workbooks.Open QTPath & "\" & QT
   
        For Each ws In wb2.Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" And ws.Name <> "Sheet2" Then
                        With ws.[A3].CurrentRegion
                                'On Error Resume Next
                                'Autofilter the late cancel date enter in B37 with dates in column 1
                                .AutoFilter 1, LCDt
                                'Autofilter the late cancel request number with request numbers in column 3
                                .AutoFilter 3, LCReq
                               
                                'Check to see if the date cell, column A, for a job has anything in it. If it doesn't, turn the autofilter off and skip to the next sheet.
                                If ws.[A3].Cells.Offset(1, 0) = "" Then
                                    .AutoFilter
                                    SheetCounter = SheetCounter + 1
                                    'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know
                                    If SheetCounter = 12 Then
                                        MsgBox "A job with the date and request number entered does not exist"
                                    End If
                                    GoTo SkipNextSheet
                                End If
                               
                                'Check the count Autofilter
                                AutoFilterCounter = .Columns(1).SpecialCells(xlCellTypeVisible).Count
                                'If value less than 2, only the heading is visible so skip to the next sheet.
                                If AutoFilterCounter < 2 Then
                                    .AutoFilter
                                    'Add 1 to a sheet counter
                                    SheetCounter = SheetCounter + 1
                                    'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know
                                    If SheetCounter = 12 Then
                                        MsgBox "A job with the date and request number entered does not exist"
                                    End If
                                    GoTo SkipNextSheet
                                End If
                               
                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        'Check if there is a service entered in column 5 of the filtered job.
                                        If .Areas(1).Cells(1, 5).Value = "" Then
                                            'Display a messagebox with a message and the sheet that has the missing service.
                                            MsgBox "There is a job in the " & ws.Name & " sheet that matches the date and request number but does not have a " & _
                                            "service type. Please add a service type to this job before continuing."
                                            Call TurnOnFunctionality
                                            .AutoFilter
                                            'Cells(32, 2).ClearContents
                                            'Cells(37, 2).ClearContents
                                            Exit Sub
                                        End If
                                        'If the service column, (5), has a value, store the service in the service variable.
                                        Service = .Areas(1).Cells(1, 5).Value
                                    End With
                               
                               
                                Dim LastRow As Long
                                LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                                Dim rng As Range: Set rng = ws.Range("A4:A" & LastRow)
                               
                                Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count
                                'if this range is greater than 1, ask the below question, else continue
                                If rws > 1 Then
                                'If ws.Range("A4", ws.Range("A4").End(xlDown)) > 1 Then
                                    Dim answer As Integer
                                    Dim RowNumber As Long
                                    Dim RowLine As Range
                                    Application.ScreenUpdating = True
                                    For Each RowLine In rng
                                        ws.Activate
                                        RowLine.EntireRow.Interior.ColorIndex = 6
                                        answer = MsgBox("Is this the job you want to apply the late cancel price too?", vbQuestion + vbYesNo + vbDefaultButton2, "Late Cancel Price")
                                        RowLine.EntireRow.Interior.ColorIndex = 0
                                        If answer = vbYes Then
                                            'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them
                                            RowNumber = RowLine.Row - 3
                                            GoTo FoundRightJob
                                        End If
                                        'If answer = vbNo
                                       
                                    Next RowLine
                                End If
FoundRightJob:
                                   
                                    'Copy data fom the job back to a calculator on the data sheet (this is the code name for sheet2) to calulcate the price again.
                                    With Data
                                        .Cells(30, 1) = CDate(LCDt)
                                        '.Cells(30, 1) = Format(Date, "d/mm/yyyy")
                                        '.Cells(30, 1).NumberFormat = "d/mm/yyyy"
                                        .Cells(30, 2) = Service
                                        'Set the hourly figure in the lateCanel table to be the LateCancelHours variable
                                        .Cells(30, 5) = LateCancelHours
                                        'A late cancel will be charged for 1 staff member attending
                                        'Therefore, set the Staff Req. figure to 1
                                        .Cells(30, 6) = 1
                                    End With
                                    On Error GoTo Price
                                        'Calculates price of late cancel on worksheet so the new price will be copied to the allocation sheet instead of the previous price
                                        Calculate
                                    LCPrice = Data.Cells(30, 8).Value
Price:
                                Select Case Err.Number
                                    Case Is = 13
                                        MsgBox "There is a problem with the spelling of the service type on the " & ws.Name & " sheet for the job that matches " _
                                        & "the date and request number. Please check the spelling and try again."
                                        'Cells(32, 2).ClearContents
                                        'Cells(37, 2).ClearContents
                                        .AutoFilter
                                        Call TurnOnFunctionality
                                        Exit Sub
                                End Select
                                On Error GoTo 0
                                With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                    Dim LTCnclDate As String
                                    .Areas(1).Cells(RowNumber, 1).Value = "LT CNCL " & .Areas(1).Cells(1, 1).Value
                                    .Areas(1).Cells(RowNumber, 8).Value = LCPrice
                                    .Areas(1).Cells(RowNumber, 9).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                                    .Areas(1).Cells(RowNumber, 10).Formula = "=RC[-1]+RC[-2]"
                                End With
                               
                             
                                .AutoFilter
                        End With
                End If

SkipNextSheet:
        Next ws
'sh.Range("B32,B37").ClearContents
Call TurnOnFunctionality

End Sub

My spreadsheet looks like this
CSS Work Allocation Sheet.67.xlsm
ABCD
3DatePurchase order #Req #Name
45/07/202195
55/07/202195
65/07/202195
75/07/202195
88/07/20215432
99/07/20215433
1010/07/20215434
1111/07/20215435
1212/07/20215436
1313/07/20215437
1414/07/20215438
1515/07/20215439
1616/07/20215440
1717/07/20215441
1818/07/20215442
1919/07/20215443
2020/07/20215444
2121/07/20215445
2222/07/20215446
2323/07/20215447
2424/07/20215448
July


These are the steps I take and the results I get
  1. I enter 5/07/2021 as the date and 95 as the request number in 4 separate rows on the July sheet
  2. I enter those numbers on the totals sheet and execute the code as above
  3. The July is activated with the first matching row highlighted and the rows are filtered to show only the rows with the date and request number so only 4 rows appear
  4. I press no and the box appears again with the following match/row highlighted
  5. I press Yes and the correct row, being the second row has the late cancel price applied to it.
  6. The date on that row now looks like this
    1. LT CNCL 5/07/2021
  7. All the rows are now unfiltered
  8. The spreadsheet looks like this
  9. CSS Work Allocation Sheet.67.xlsm
    ABCD
    3DatePurchase order #Req #Name
    45/07/202195
    5LT CNCL 5/07/202195
    65/07/202195
    75/07/202195
    88/07/20215432
    99/07/20215433
    1010/07/20215434
    1111/07/20215435
    1212/07/20215436
    1313/07/20215437
    1414/07/20215438
    1515/07/20215439
    1616/07/20215440
    1717/07/20215441
    1818/07/20215442
    1919/07/20215443
    2020/07/20215444
    2121/07/20215445
    2222/07/20215446
    2323/07/20215447
    2424/07/20215448
    July
  10. This is where I enter the numbers
  11. CSS Work Allocation Sheet.67.xlsm
    ABCDEFG
    29Late Cancel
    30To enter a job as a late cancel, enter a request number first in B32, followed by the date of the proposed job in B37 then press enter and select the Add Late Cancel button.
    31Request number
    3295
    33Everytime you leave this sheet and return, this will be 3
    34Hours charged for a late cancel
    353
    36Date
    375/07/2021
    Totals
  12. If I then run the code again from the totals sheet without changing anything, the July sheet is activated again but now there are only 3 filtered results. Only rows 4,6 & 7 are visible with row 4 being highlighted
  13. I press No on the question and seeing as though row 6 is the next filtered row, I would expect row 6 to be highlighted as the next row but after I press No, there are no rows that are highlighted. My guess is it is still highlighting row 5 because after you press No again, row 6 becomes highlighted.
    1. Not sure why it is doing that but it needs to cycle through the cells that match the filter exactly, ie, 5/07/2021, not LT CNCL 5/07/2021 for the date and 95 for the request number or maybe cycle through the visible rows instead
  14. Not sure if this has an impact but the date column is formatted as date



Another issue is if I press no 4 times on the question asking is this the row to apply it to, none of the rows will be touched but the data that was meant to go in the row will overwrite the heading in row 3.

So after this happens, the heading of my spreadsheet looks like this
CSS Work Allocation Sheet.67.xlsm
ABCDEFGHIJK
3LT CNCL 5/07/2021Purchase order #Req #NameServiceRequesting OrganisationCaseworker Name219.3021.93241.23Allocated to
July
Cell Formulas
RangeFormula
I3I3=IF(E3="Activities",0,H3*0.1)
J3J3=I3+H3


When it should look like this
CSS Work Allocation Sheet.67.xlsm
ABCDEFGHIJK
2
3DatePurchase order #Req #NameServiceRequesting OrganisationCaseworker NamePrice ex. GSTGSTPrice inc. GSTAllocated to
July


Thanks
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
This has got nothing to do with your original question, I suggest you start a new thread for this.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,424
Messages
5,642,037
Members
417,251
Latest member
Dordrecht

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top