0

I am getting a runtime error on this exact line.

Range("Aqual").Offset(0, i).Interior.color = rg.Offset(0, i + 11).Interior.color

I am simply trying to match color of a range in one worksheet to another range in a different worksheet. I have tried various troubleshooting rewrites of this line and tried to contain this out into a separate sub and it still fails. (I have these troubleshooting lines commented out and they all also fail.) I still can't get it and have wasted days trying to figure this out. The Range("Aqual") is a named range in sheet1 "F8". The for loop is so it moves across 6 columns. My worksheets are not protected nor is my workbook.

Option Explicit

Dim rg As Range
Dim i As Integer

Function posABC(ByVal A As String, ByVal B As String, ByVal C As String, ByVal D As String, ByVal G As Integer) As String
    On Error GoTo errormsg
    Application.EnableEvents = False

    Dim output As String
    Dim code As String
    Dim priKVA As String
    Dim secKVA As String
    Dim readCode As String
    Dim secOffset As Integer
    Dim writeRange As Range
    Dim readRange As Range
    
    output = "empty code"
    code = A & B & C
    If G >= 0 And G <= 3 Then
        secOffset = 0
    Else
        secOffset = G - 3
    End If
    
    For Each rg In Range("tblPosA")
        readCode = rg.Value & rg.Offset(0, 1).Value & rg.Offset(0, 2).Value
        If readCode = code Then
            priKVA = rg.Offset(0, 4).Value
            
            If D = "1" Then
                secKVA = rg.Offset(1, 5 + secOffset).Value
            Else
                secKVA = rg.Offset(0, 5 + secOffset).Value
            End If
            
            For i = 0 To 5
               
               Range("Aqual").Offset(0, i).Interior.color = rg.Offset(0, i + 11).Interior.color
                
'               Set writeRange = Range("Aqual").Offset(0, i)
'               Set readRange = rg.Offset(0, i + 11)
'               writeRange.Interior.color = readRange.Interior.color
               
'                Call matchColor(Range("Aqual").Offset(0, i), rg.Offset(0, i + 11))
                
'               Range("F8").Interior.color = RGB(0, 255, 0)
               
               
'               Select Case True
'                    Case rg.Offset(0, i + 11).Interior.color = vbRed
'                        Range("Aqual").Offset(0, i).Interior.color = vbRed
'                    Case rg.Offset(0, i + 11).Interior.color = vbGreen
'                        Range("Aqual").Offset(0, i).Interior.color = vbGreen
'                    Case rg.Offset(0, i + 11).Interior.color = vbYellow
'                        Range("Aqual").Offset(0, i).Interior.color = vbYellow
'                End Select
            Next i
            
            Exit For
        End If
    Next rg
    
    output = "Primary kVA= " & priKVA & vbNewLine & "Secondary kVA= " & secKVA
            
    posABC = output
    Application.EnableEvents = True
errormsg:
    MsgBox Err.Description & " " & Err.Number
End Function

If use this sub for the operation it still fails.

Sub matchColor(ByVal writeRange As Range, ByVal readRange As Range)
    writeRange.Interior.color = readRange.Interior.color
End Sub

And for some reason I don't know. This sub does work when I was trying to isolate the failure.

Sub colortest()
  Dim writeRange As Range
  Dim readRange As Range
  Dim rg As Range
  
  Set rg = Sheet7.Range("L6")
  Set writeRange = Range("Aqual").Offset(0, 0)
  Set readRange = rg.Offset(0, i)
  
  writeRange.Interior.color = readRange.Interior.color
      
End Sub
Stephen Antosz
  • 1
  • 1
  • And how do you call `posABC`? – GSerg Aug 17 '21 at 15:19
  • Any reason to have rg and i module level variables? – Vincent G Aug 17 '21 at 15:21
  • In a separate Sub, can you write a single line which sets the interior color of a cell? It's odd that in all your lines `color` is not proper case... Have you named a module, global variable or something else as `color` ? – Tim Williams Aug 17 '21 at 15:25
  • Does this routine actually change any colors, or does it fail the first time the line is hit? What is the value of i where it fails, and when it fails, also try to debug.print both of these: `Range("Aqual").Offset(0, i).Interior.color` and `rg.Offset(0, i + 11).Interior.color` to help troubleshoot the error. Do both of those contain values? – Chris Strickland Aug 17 '21 at 15:32
  • @GSerg - posABC is a function in a cell of my worksheet 1. So it's initiated on any worksheet change. – Stephen Antosz Aug 17 '21 at 15:54
  • @VincentG I use the "rg" and "i" variables in other functions in the same module. I've tried putting them as dim in each and it made no difference. – Stephen Antosz Aug 17 '21 at 15:55
  • @ChrisStrickland it fails once it tries to operate that line so no color changes occur. i starts off as 0. I can debug print both .interior.color properties and the ranges show up fine in the watch window. – Stephen Antosz Aug 17 '21 at 16:08
  • @TimWilliams I have tried to sub out with byval variables and the operation is still failing. More curiously I can write a sub by itself that does work for some reason I don't understand. – Stephen Antosz Aug 17 '21 at 16:56
  • `some reason I don't understand` - look at the top of your question then. – GSerg Aug 17 '21 at 17:10
  • yeah thanks everyone. It seems I broke a cardinal rule of not modifying cells in a function. – Stephen Antosz Aug 17 '21 at 17:23

0 Answers0