How to change the color of a cell on another worksheet based on VBA code running for a 2nd worksheet

[philwojo]

So I have some error checking code that is working, it is doing what I need it to do at least. But when it finds an error I want it to not only change the cells on the worksheet I am doing for the checking, but also on a 2nd worksheet. The only thing is that the cell on the 2nd worksheet is going to be like 23 rows lower down that it is on the 1st worksheet.

Here is the code I am working with now. The code toward the end that I have in bold and red is the line I can't get to work. I want to add this to each of the error checking sections, just testing to get it working first in this one section where I know my test data has an error.

Sub Countcommas()

Application.EnableEvents = False
Application.ScreenUpdating = False


Worksheets("Raw Data").Activate


' ****************************************************************
  Dim WhatChanged As Range, Cell As Range, CommaCount As Long
  Dim Comma1K As Integer, Comma2K As Integer, Comma3K As Integer, Comma5K As Integer
  Dim CommaErrorCount As Integer, CommaRng
' ****************************************************************
  
' ****************************************************************
'Set the ranges to check.
    Set WhatChanged = Range("B2", Range("B" & Rows.Count).End(xlUp))
    Set CommaRng = Range("B2", Range("B" & Rows.Count).End(xlUp))
'Set the Data field number of commas per data field type
'A value of 4 would indicate 5 data fields, meaning 4 commas present
    Comma1K = 4
    Comma2K = 38
    Comma3K = 14
    Comma5K = 3
    CommaErrorCount = 0
' ****************************************************************
  
With ThisWorkbook.Worksheets("Raw Data")


  If Not WhatChanged Is Nothing Then
     For Each Cell In CommaRng
      CommaCount = UBound(Split(Cell.Value, ","))
      If CommaCount >= 0 Then Cell.Offset(, -1).Value = CommaCount


'Check to see what kind of Data Input this is (1000, 2100, 3000 or 5000) to determine how many commas
'Should be in each data field
      If Left((Cell.Value), 4) = 1000 Then
'If the number of commas is not correct it will flag the cell red.
        If CommaCount <> Comma1K Then
            CommaErrorCount = CommaErrorCount + 1
            Range(Cell.Offset(0, -1), Cell.Offset(0, 2)).Interior.Color = vbRed
            Range(Cell.Offset(0, -1), Cell.Offset(0, 2)).Font.Bold = True
            Range(Cell.Offset(0, -1), Cell.Offset(0, 2)).Font.Color = vbYellow
        End If
        ElseIf Left((Cell.Value), 4) = 2100 Then
            If CommaCount <> Comma2K Then
                CommaErrorCount = CommaErrorCount + 1
                Range(Cell.Offset(0, -1), Cell.Offset(0, 2)).Interior.Color = vbRed
                Range(Cell.Offset(0, -1), Cell.Offset(0, 2)).Font.Bold = True
                Range(Cell.Offset(0, -1), Cell.Offset(0, 2)).Font.Color = vbYellow
            End If
        ElseIf Left((Cell.Value), 4) = 3000 Then
            If CommaCount <> Comma3K Then
                CommaErrorCount = CommaErrorCount + 1
                Range(Cell.Offset(0, -1), Cell.Offset(0, 2)).Interior.Color = vbRed
                Range(Cell.Offset(0, -1), Cell.Offset(0, 2)).Font.Bold = True
                Range(Cell.Offset(0, -1), Cell.Offset(0, 2)).Font.Color = vbYellow
[COLOR=#ff0000][B]                Worksheets("PIF Checker Output - Horz").Range(Cell.Offset(23, 0)).Interior.Color = vbRed[/B][/COLOR]
            End If
        ElseIf Left((Cell.Value), 4) = 5000 Then
            If CommaCount <> Comma5K Then
                CommaErrorCount = CommaErrorCount + 1
                Range(Cell.Offset(0, -1), Cell.Offset(0, 2)).Interior.Color = vbRed
                Range(Cell.Offset(0, -1), Cell.Offset(0, 2)).Font.Bold = True
                Range(Cell.Offset(0, -1), Cell.Offset(0, 2)).Font.Color = vbYellow
            End If
        End If
    Next
    
'Count the number of data entries that didn't have the correct amount of commas.  If it is more than zero (0) then
'This will change the verbiage of cell A1 from all non-bold and black text, to contain partial bold text
'And turn it Red to make it stand out to indicate an error.
    Range("A1").Value = "Number of commas in the data fields. This will vary based on the data type." & vbLf & vbLf & "# of entries with less than the proper amount of commas = " & CommaErrorCount
    If CommaErrorCount > 0 Then
        Range("a1").Interior.Color = vbBlack
        Range("A1").Characters(1, 77).Font.Color = vbWhite
        Range("A1").Characters(1, 77).Font.Bold = False
        Range("A1").Characters(78, 59).Font.Color = vbRed
        Range("A1").Characters(78, 59).Font.Bold = True
        Range("A1").Characters(136, 999).Font.Bold = True
        Range("A1").Characters(136, 114).Font.Color = vbYellow
        Range("A1").Characters(136, 114).Font.Size = 16
    End If
  End If
End With
' ****************************************************************




Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

 

[Yongle]

Cell is already set as a range in the other worksheet
- get the address of Cell
- place that inside Range(..)
- apply Offset to that

[B]Worksheets("PIF Checker Output - Horz").Range(Cell.Address).Offset(23, 0).Interior.Color = vbRed[/B]

Or with Cells avoid using Offset by adopting this syntax

[B]Worksheets("PIF Checker Output - Horz").Cells(Cell.Row + 23,Cell.Column).Interior.Color = vbRed[/B]

 

[philwojo]

Thanks, I used the first one for now and it is working, I appreciate the assistance!

Phil