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

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533
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.

Code:
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Cell is already set as a range in the other worksheet
- get the address of Cell
- place that inside Range(..)
- apply Offset to that
Code:
[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
Code:
[B]Worksheets("PIF Checker Output - Horz").Cells(Cell.Row + 23,Cell.Column).Interior.Color = vbRed[/B]
 
Last edited:
Upvote 0
Thanks, I used the first one for now and it is working, I appreciate the assistance!

Phil
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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
Back
Top