Problem with Dynamic Named Range and CF vba

[Toonies]

Hi and thanks for looking at my question, which is

COPY of FILE Book1.vs.xls

I have 5 Dynamic Named Ranges on sheet "ABSENCE CODES"

Tbl1

=OFFSET('ABSENCE CODES'!$A$1,0,0,COUNTA(Data!$A:$A),1)

Tbl2

=OFFSET('ABSENCE CODES'!$B$1,0,0,COUNTA(Data!$B:$B),1)

Tbl3

=OFFSET('ABSENCE CODES'!$C$1,0,0,COUNTA(Data!$C:$C),1)

Tbl4

=OFFSET('ABSENCE CODES'!$D$1,0,0,COUNTA(Data!$D:$D),1)

Tbl5

=OFFSET('ABSENCE CODES'!$E$1,0,0,COUNTA(Data!$E:$E),1)

and cannot get them to work in my VBA code on sheet "DRAFT"

If I reference each cell individually i.e. "A1" etc. then my VBA works

here is the VBA Code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim isect As Range, cell As Range
    Dim CellColour As Integer
Dim TimeStrText As String
Dim UserInput
Dim rValues As Range



    If Not Application.Intersect(Target, Range("C4:AI43")) Is Nothing Then
    Target(1).Value = UCase(Target(1).Value)
 
    For Each cell In Range("C4:C43, H4:H43, M4:M43, R4:R43, W4:W43, AB4:AB43, AG4:AG43")
        If cell.Value = Sheet2("ABSENCE CODES").Range("Tbl1") Then
            cell.Offset(0, 0).Interior.ColorIndex = 4
            cell.Offset(0, 0).Font.ColorIndex = 1
            cell.Offset(0, 1).Interior.ColorIndex = 4
            cell.Offset(0, 1).Font.ColorIndex = 4
            cell.Offset(0, 2).Interior.ColorIndex = 0
            cell.Offset(0, 2).Font.ColorIndex = 2
         
           ElseIf cell.Value = Sheet2("ABSENCE CODES").Range("Tbl2") Then
            cell.Offset(0, 0).Interior.ColorIndex = 3
            cell.Offset(0, 0).Font.ColorIndex = 1
            cell.Offset(0, 1).Interior.ColorIndex = 3
            cell.Offset(0, 1).Font.ColorIndex = 3
            cell.Offset(0, 2).Interior.ColorIndex = 0
            cell.Offset(0, 2).Font.ColorIndex = 2
           
           ElseIf cell.Value = Sheet2("ABSENCE CODES").Range("Tbl3") Then
            cell.Offset(0, 0).Interior.ColorIndex = 6
            cell.Offset(0, 0).Font.ColorIndex = 1
            cell.Offset(0, 1).Interior.ColorIndex = 6
            cell.Offset(0, 1).Font.ColorIndex = 6
            cell.Offset(0, 2).Interior.ColorIndex = 0
            cell.Offset(0, 2).Font.ColorIndex = 2
         
           ElseIf cell.Value = Sheet2("ABSENCE CODES").Range("Tbl4") Then
            cell.Offset(0, 0).Interior.ColorIndex = 8
            cell.Offset(0, 0).Font.ColorIndex = 1
            cell.Offset(0, 1).Interior.ColorIndex = 8
            cell.Offset(0, 1).Font.ColorIndex = 8
            cell.Offset(0, 2).Interior.ColorIndex = 0
            cell.Offset(0, 2).Font.ColorIndex = 2
            
          ElseIf cell.Value = Sheet2("ABSENCE CODES").Range("Tbl5") Then
            cell.Offset(0, 0).Interior.ColorIndex = 45
            cell.Offset(0, 0).Font.ColorIndex = 1
            cell.Offset(0, 1).Interior.ColorIndex = 45
            cell.Offset(0, 1).Font.ColorIndex = 45
            cell.Offset(0, 2).Interior.ColorIndex = 0
            cell.Offset(0, 2).Font.ColorIndex = 2
         
        ElseIf cell.Value > 0 Then
            cell.Offset(0, 0).Interior.ColorIndex = 0
            cell.Offset(0, 1).Interior.ColorIndex = 0
            cell.Offset(0, 2).Interior.ColorIndex = 0
            
            cell.Offset(0, 0).Font.ColorIndex = 1
            cell.Offset(0, 1).Font.ColorIndex = 1
            cell.Offset(0, 2).Font.ColorIndex = 1
            
        ElseIf cell.Value = "" Then
            cell.Offset(0, 0).Interior.ColorIndex = 0
            cell.Offset(0, 1).Interior.ColorIndex = 0
            cell.Offset(0, 2).Interior.ColorIndex = 0
            
            cell.Offset(0, 0).Font.ColorIndex = 1
            cell.Offset(0, 1).Font.ColorIndex = 1
            cell.Offset(0, 2).Font.ColorIndex = 1
            
        End If
        
    Next cell
    
       End If
        
    

On Error GoTo EndMacro
If Not Application.Intersect(Target, Range("C4:D43, H4:I43, M4:N43, R4:S43, W4:X43, AB4:AC43, AG4:AH43")) Is Nothing Then
    Target(1).Value = UCase(Target(1).Value)
    If Application.Intersect(Target, Range("C4:E43, H4:J43, M4:O43, R4:T43, W4:Y43, AB4:AD43, AG4:AI43")) Is Nothing Then
    If Not Intersect(Target, [E44, J44, O44, T44, Y44, AD44, AI44]) Is Nothing Then
        If Not Selection.Cells.Count = 1 Then
       ' Application.Undo
            Application.EnableEvents = False
            If Target.Value < 1 Then
                UserInput = Format(Int(Target.Value * 24) * 100 + ((Target.Value * 24) - Int(Target.Value * 24)) * 60, "000")
            Else
                UserInput = Format(Target.Value, "000")
            End If
                If Len(UserInput) > 1 Then
                    Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
                End If
                Application.EnableEvents = True
            End If
        End If
        
    End If
    
End If
    If Target.Cells.Count > 1 Then
       
    End If
    If Target.Value = "" Then
        
    End If
    If Target.Column = "1" Then
        
    End If
    Application.EnableEvents = False
    With Target
        If .HasFormula = False Then
                Select Case Len(.Value)
                    Case 1 ' e.g., 1 = 00:01
                        TimeStrText = "00:0" & .Value
                    Case 2 ' e.g., 12 = 00:12 AM
                        TimeStrText = "00:" & .Value
                    Case 3 ' e.g., 735 = 7:35 AM
                        TimeStrText = Left(.Value, 1) & ":" & _
                        Right(.Value, 2)
                    Case 4 ' e.g., 1234 = 12:34
                        TimeStrText = Left(.Value, 2) & ":" & _
                        Right(.Value, 2)
                    Case 5 ' e.g., 1 = 00:00
                        TimeStrText = "24:00" & .Value
                    Case Else
                        Err.Raise 0
                End Select
                    .Value = TimeValue(TimeStrText)
            
End If

End With

Application.EnableEvents = True

EndMacro:
Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

Any pointers or suggestions would be helpful

[Toonies]

Hope that its possible

[Norie]

How don't they work?

Why do you have a reference to a sheet 'Data' in the named ranges?

[Toonies]

Hi Norie,

I some how input the wrong range,

I have corrected that and removed Data! ref

however when I try the amended Range I get the following error

Run-time error '438';

Object doesn't support this property or method

when I Debug it highlights this line

If cell.Value = Sheet2("ABSENCE CODES").Range("Tbl1") Then

here is the amended file

Book1.vs1.xls

thanks

[Norie]

Sheet2 should be Sheets

[Toonies]

Hi I changed it to

If cell.Value = Sheets("ABSENCE CODES").Range("Tbl1") Then

and get the following error message

Run-time error '13';

Type mismatch

[Toonies]

Hi I changed it to

If cell.Value = Sheets("ABSENCE CODES").Range("Tbl1") Then

and get the following error message

Run-time error '13';

Type mismatch

[Norie]

How many cells are in Tbl1?

If it's more than one you'll get the error you describe.

[Toonies]

yes there is more than 1, is there a workaround that I could use instead?

[Norie]

Are you trying to check if the value in cell is in the range 'TblX'?

[Toonies]

Yes I want to check if the value is in ranges Tbl1, 2, 3, 4, 5

if the value is found then dependant upon which one, then colour the cell a specific colour in the "Draft" sheet

[Toonies]

Hi I'm still looking for help with this question