Macro to run when cell on other sheet changes

J

jmarting13

New Member
Joined
Mar 2, 2021
Messages
39
Platform
  1. Windows
I am trying to make it so when a cell value is changed, all the sheets reset their color to black, then any changes made after that use a red font. I am pretty clueless at VBA, but have gotten the code below to work. It only works for my sheet1. Basically I want the font on all the sheets to change to black when cell H3 in sheet1 is changed. Then If the value is revision a-j, I want the font color to be red. can anyone help me with this? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$3" Then
Range("A1:J37").Font.Color = vbBlack
End If




If [H3] = "Revision A" Then
Target.Font.Color = RGB(255, 0, 0)
Else
If [H3] = "Revision B" Then
Target.Font.Color = RGB(255, 0, 0)
Else
If [H3] = "Revision C" Then
Target.Font.Color = RGB(255, 0, 0)
Else
If [H3] = "Revision D" Then
Target.Font.Color = RGB(255, 0, 0)
Else
If [H3] = "Revision E" Then
Target.Font.Color = RGB(255, 0, 0)
Else
If [H3] = "Revision F" Then
Target.Font.Color = RGB(255, 0, 0)
Else
If [H3] = "Revision G" Then
Target.Font.Color = RGB(255, 0, 0)
Else
If [H3] = "Revision H" Then
Target.Font.Color = RGB(255, 0, 0)
Else
If [H3] = "Revision I" Then
Target.Font.Color = RGB(255, 0, 0)
Else
If [H3] = "Revision J" Then
Target.Font.Color = RGB(255, 0, 0)
Else
Target.Font.Color = RGB(0, 0, 0)
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Hello Jmartig13 and welcome to MrExcel forum.
Here is one preliminar code. See if can help.
This goes to the sheet1 module.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
       
        If Target.Address = "$H$3" And Not _
              Target = "Revision A" And Not _
              Target = "Revision B" And Not _
              Target = "Revision C" And Not _
              Target = "Revision D" And Not _
              Target = "Revision E" And Not _
              Target = "Revision F" And Not _
              Target = "Revision G" And Not _
              Target = "Revision H" And Not _
              Target = "Revision I" And Not _
              Target = "Revision J" Then
              For vN = 1 To ActiveWorkbook.Sheets.Count
                        Sheets(vN).Range("A1:J37").Font.Color = vbBlack
              Next vN
       Else
              For vN = 1 To ActiveWorkbook.Sheets.Count
                        Sheets(vN).Range("A1:J37").Font.Color = vbRed
              Next vN
       End If

End Sub
 
Upvote 0
EXCEL MAX said:
Hello Jmartig13 and welcome to MrExcel forum.
Here is one preliminar code. See if can help.
This goes to the sheet1 module.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
      
        If Target.Address = "$H$3" And Not _
              Target = "Revision A" And Not _
              Target = "Revision B" And Not _
              Target = "Revision C" And Not _
              Target = "Revision D" And Not _
              Target = "Revision E" And Not _
              Target = "Revision F" And Not _
              Target = "Revision G" And Not _
              Target = "Revision H" And Not _
              Target = "Revision I" And Not _
              Target = "Revision J" Then
              For vN = 1 To ActiveWorkbook.Sheets.Count
                        Sheets(vN).Range("A1:J37").Font.Color = vbBlack
              Next vN
       Else
              For vN = 1 To ActiveWorkbook.Sheets.Count
                        Sheets(vN).Range("A1:J37").Font.Color = vbRed
              Next vN
       End If

End Sub
Click to expand...
Thanks for the help! I had to change
Sheets(vN).Range("A1:J37").Font.Color = vbRed to Target.Font.Color = RGB(255, 0, 0), just because I wanted to make only the edits red, not the entire page. The only problem is now when I have the options "quote" or "RFI" chosen, the font is now red. basically if it is any of the options other than Revision a-j, I want the target.font.color to be black. Thanks again!
 
Upvote 0
My main problem is getting the code I attached to work on sheets 2-5. It works perfectly on sheet 1 but not the others for some reason
 
Upvote 0
Try to put this code in "Worksheet_Change" event and look if is closer to your needs...
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$H$3" Then
        For vN = 1 To ActiveWorkbook.Sheets.Count
            If Target = "Revision A" Or _
                Target = "Revision B" Or _
                Target = "Revision C" Or _
                Target = "Revision D" Or _
                Target = "Revision E" Or _
                Target = "Revision F" Or _
                Target = "Revision G" Or _
                Target = "Revision H" Or _
                Target = "Revision I" Or _
                Target = "Revision J" Then
                Sheets(vN).Range("A1:J37").Font.Color = vbRed
                Sheets(vN).Range(Target.Address).Font.Color = vbBlack
            Else
                Sheets(vN).Range("A1:J37").Font.Color = vbBlack
            End If
        Next vN
    End If

End Sub
 
Upvote 0
EXCEL MAX said:
Try to put this code in "Worksheet_Change" event and look if is closer to your needs...
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$H$3" Then
        For vN = 1 To ActiveWorkbook.Sheets.Count
            If Target = "Revision A" Or _
                Target = "Revision B" Or _
                Target = "Revision C" Or _
                Target = "Revision D" Or _
                Target = "Revision E" Or _
                Target = "Revision F" Or _
                Target = "Revision G" Or _
                Target = "Revision H" Or _
                Target = "Revision I" Or _
                Target = "Revision J" Then
                Sheets(vN).Range("A1:J37").Font.Color = vbRed
                Sheets(vN).Range(Target.Address).Font.Color = vbBlack
            Else
                Sheets(vN).Range("A1:J37").Font.Color = vbBlack
            End If
        Next vN
    End If

End Sub
Click to expand...
I'm still confused on how to get this to work on my other sheets as well. thanks
 
Upvote 0
In the VBA editor "This Workbook" module paste this code.
I suppose you will understand logic.
VBA Code: 
Dim vN As Integer

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Address = "$H$3" And _
        Sh.Name = "Sheet2" Or _
        Sh.Name = "Sheet5" Then
        For vN = 1 To ActiveWorkbook.Sheets.Count
            If Target = "Revision A" Or _
                Target = "Revision B" Or _
                Target = "Revision C" Or _
                Target = "Revision D" Or _
                Target = "Revision E" Or _
                Target = "Revision F" Or _
                Target = "Revision G" Or _
                Target = "Revision H" Or _
                Target = "Revision I" Or _
                Target = "Revision J" Then
                Sheets(vN).Range("A1:J37").Font.Color = vbRed
                Sheets(vN).Range(Target.Address).Font.Color = vbBlack
            Else
                Sheets(vN).Range("A1:J37").Font.Color = vbBlack
            End If
        Next vN
    End If

End Sub
 
Upvote 0
EXCEL MAX said:
In the VBA editor "This Workbook" module paste this code.
I suppose you will understand logic.
VBA Code: 
Dim vN As Integer

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Address = "$H$3" And _
        Sh.Name = "Sheet2" Or _
        Sh.Name = "Sheet5" Then
        For vN = 1 To ActiveWorkbook.Sheets.Count
            If Target = "Revision A" Or _
                Target = "Revision B" Or _
                Target = "Revision C" Or _
                Target = "Revision D" Or _
                Target = "Revision E" Or _
                Target = "Revision F" Or _
                Target = "Revision G" Or _
                Target = "Revision H" Or _
                Target = "Revision I" Or _
                Target = "Revision J" Then
                Sheets(vN).Range("A1:J37").Font.Color = vbRed
                Sheets(vN).Range(Target.Address).Font.Color = vbBlack
            Else
                Sheets(vN).Range("A1:J37").Font.Color = vbBlack
            End If
        Next vN
    End If

End Sub
Click to expand...
Still doesnt work unfortunately. It only changes to red for the first revision and doesn't work on the other sheets
 
Upvote 0
jmarting13 said:
Still doesnt work unfortunately. It only changes to red for the first revision and doesn't work on the other sheets
Click to expand...
I'm mainly looking for how to copy the code so it works on all the sheets and how to reference sheet 1 on sheets 2-5
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
VBA help: alert if end date entered is earlier than start date range
Replies
4
Views
157
Peter_SSs
Peter_SSs
G
  • Question
Activex Check Box VBA Code not working automatically
Replies
6
Views
264
ganeshadu
G
A
  • Solved
Excel VBA Phone Number Format Issue
Replies
4
Views
95
AnnAnn
A
A
  • Question
VBA highlight blank cells in a range and their corresponding headers
Replies
7
Views
145
AnnAnn
A
A
  • Question
VBA Highlight cell if contains specific text
Replies
4
Views
145
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,925
Messages
6,122,303
Members
449,078
Latest member
nonnakkong

Share this page

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