Highlighting a range

[dpaton05]

This code highlights an entire row.

        For r = 4 To lr
            If Cells(r, 1).Value = Dt And Cells(r, 3).Value = Req Then
                'Highlight the row colour so the user knows what row the question is relating to
                Rows(r).Interior.ColorIndex = 6
                answer = MsgBox("Is this the job you want to cancel?", vbQuestion + vbYesNo + vbDefaultButton2, "Job Cancellation")
                Rows(r).Interior.ColorIndex = 0

How do I change it so that the row is only highlighted from column A to column O?

 

[dpaton05]

Actually, my code didn't work. Thanks for your code but it only works if the only jobs on the sheet are the jobs that have that date and request number. If there are other dates, it won't work.

 

[dpaton05]

Here is my code

Sub LateCancel()
Dim Sh As Worksheet, Service As String, LCPrice As Currency, answer As String, n As Long, mth As String
Dim lr As Long, r As Long, t As Long
Set Sh = Sheets("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 = CDate(Sh.Cells(37, 2).Value)
Dim LateCancelHours As String: LateCancelHours = Sh.Cells(35, 2).Value
mth = MonthName(Month(LCDt))
'If the date of the job in the calculator of sheet2 is after the 26th, assign the month where it goes to the following month
If Day(LCDt) >= 26 Then mth = MonthName(Month(LCDt) + 1)
Worksheets(mth).Activate
n = 0
t = 0
lr = Cells(Rows.Count, "A").End(xlUp).Row
    'Start in row 4 as that's where the data starts
    For r = 4 To lr
        If Cells(r, 1).Value <> LCDt And Cells(r, 3).Value <> LCReq Then t = t + 1
        If Cells(r, 1).Value = LCDt And Cells(r, 3).Value = LCReq Then
                    'store the service in the service variable.
                If Cells(r, 5) = "" Then
                    MsgBox "There is a job in row " & r & " on the " & mth & " sheet that matches the date and " & _
                    "request number but does not have a service type. Please add a valid service type before continuing."
                    Exit Sub
                End If
                Service = Cells(r, 5).Value
                Range("A" & r & ":" & "O" & r).Interior.ColorIndex = 6
                answer = MsgBox("Is this the job you want to apply the late cancel price too?", vbQuestion + vbYesNo + vbDefaultButton2, "Late Cancel Price")
                Rows(r).Interior.ColorIndex = 0
                If answer = vbNo Then
                    'Add 1 to the counter of If No is pressed
                    n = n + 1
                End If
                    If answer = vbYes Then
                        If Cells(r, 5) = "Carer Respite" Then
                            MsgBox "Carer respite cannot have the Late Cancel price applied to it."
                            Exit Sub
                        End If
                        With Data
                            .Cells(30, 1) = CDate(LCDt)
                            .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
                            '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 = .Cells(30, 8).Value
                       End With
                        Dim LTCnclDate As String
                    Cells(r, 1).Value = "LT CNCL " & Cells(r, 1).Value
                    Cells(r, 8).Value = LCPrice
                    Cells(r, 9).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                    Cells(r, 10).Formula = "=RC[-1]+RC[-2]"
                    Exit Sub
                End If
        Else
        End If
    Next r
        If n > 0 Then MsgBox "You have chosen to not apply the late cancel price to any of the " & n & " jobs matching the date and request number."
        If t > 0 Then MsgBox "There is no job with the date: " & LCDt & " and the request number: " & LCReq & " in the sheet of " & mth & "."
    Sh.Activate
End Sub

 

[Michael M]

You did say

If there is no job with the date and request number

....Note the AND.
So what are you actually looking for ??
The code I provided looks for both not being TRUE

 

[dpaton05]

I am sorry about the confusion Michael. I don't know what happened as it seemed to be giving an error in certain circumstances and not in others but it has stopped and I am not sure why

 

[Michael M]

So is it OK, or .......???

 

[dpaton05]

This is so strange! I could run the LateCancel sub using a button and I would click on a separate button to run the transfer sub but nothing would happen. I then ran the tranfer sub from within the VBE and it worked as it should. I then tried to run it using the button again and it worked fine.

It is strange as it works sometimes and not others.

 

[Michael M]

Sounds like a good time to close Excel and restart !

 

[dpaton05]

Thanks Michael, that seems to have fixed it.