Defining a range

[jmarting13]

Private Sub Worksheet_Calculate()
Dim Xrg As Range
Set Xrg = Range("K1")
If Not Intersect(Xrg, Range("K1")) Is Nothing Then
MsgBox "This is fun"
Range("A1:J37").Font.Color = vbBlack
End If




If Range("K1").Value = "Quote" Then
target.Font.Color = vbBlack
Else
target.Font.Color = vbRed
End If
End Sub

I keep getting an error on the two lines of code that start with "target.font.color"
Basically if the value in K1 is "Quote", I want the main font color to be black, and if it isn't then red.
How do I define a range for the target.font.color?

 

[jmarting13]

Here is the tricky part:

Your original sheet uses a "Worksheet_Change" event procedure code.l This only works on cells which are updated manually.
So as you manually update cell H3 on the sheet, the code runs.
"Worksheet_Change" event procedure code can identify exactly which cell was just identified (this is the "Target" cell).

The other sheet you want to run when the value returned by a formula changes. "Worksheet_Change" event procedures do NOT run when values return by formulas change.
"Worksheet_Calculate" event procedures run whenever any cell on the sheet is recalculated (i.e. a formula value changes), but it cannot identify which cell changed. It only knows that some cell somewhere on the sheet changed. So it will run whenever any cell with a formula changes on your sheet.

When changing the font color, you don't really say "future updates should be this color". What you do is assign a font color to certain cells.
Now, you can include logic to say only change the cell color based on whether or not the cell is blank or has some value, and you can apply it to a range of cells, but you would need to pre-define that.

One option would be to have VBA code automatically update some unused cell with, say, a font color name (based on whatever conditions you set). So you could "toggle" this back-and-forth.
You could then have a "Worksheet_Change event procedure on this other sheet look at that value when certain cells are updated, and color the cell based on that value.
If you wanted to do something like this, we would need to know the very specific details of how this should work (exactly which cells are involved, under what conditions things change to what, etc).

I don't want to waste too much of your time, but I'm 100% open to whatever changes I need to make. How I'd like it to work is if the value of cell H3 on sheet1 is Revision A, Revision B, Revision C, ect.. all the way to Revision J, I'd like the font color to be red, and if it's anything else, I'd like it to be black. Every time cell H3 is changed, I'd also like to reset the font of everything in the entire workbook to black.

In a perfect world, how the sheet would work in my head is when someone clicks Revision A, the all their changes are recorded as red font so our administration can double check the changes. If we need to revise the project again, they would click Revision B and the sheet would reset to black and all further revisions would be noted in red ect..

What else do I need to let you know? Thanks so much.

 

[jmarting13]

Here is the tricky part:

Your original sheet uses a "Worksheet_Change" event procedure code.l This only works on cells which are updated manually.
So as you manually update cell H3 on the sheet, the code runs.
"Worksheet_Change" event procedure code can identify exactly which cell was just identified (this is the "Target" cell).

The other sheet you want to run when the value returned by a formula changes. "Worksheet_Change" event procedures do NOT run when values return by formulas change.
"Worksheet_Calculate" event procedures run whenever any cell on the sheet is recalculated (i.e. a formula value changes), but it cannot identify which cell changed. It only knows that some cell somewhere on the sheet changed. So it will run whenever any cell with a formula changes on your sheet.

When changing the font color, you don't really say "future updates should be this color". What you do is assign a font color to certain cells.
Now, you can include logic to say only change the cell color based on whether or not the cell is blank or has some value, and you can apply it to a range of cells, but you would need to pre-define that.

One option would be to have VBA code automatically update some unused cell with, say, a font color name (based on whatever conditions you set). So you could "toggle" this back-and-forth.
You could then have a "Worksheet_Change event procedure on this other sheet look at that value when certain cells are updated, and color the cell based on that value.
If you wanted to do something like this, we would need to know the very specific details of how this should work (exactly which cells are involved, under what conditions things change to what, etc).

I don't want to waste too much of your time, but I'm 100% open to any changed you'd suggest.

In a perfect world how the sheet would work is:
When the sheet is filled out with Quote or RFI selected in cell H3, nothing is special, and the font is black.
If we need to revise the sheet, someone selects Revision A in H3, and the font now turns to red, marking any changes and allowing our administration to see the changes.
If we need to revise the sheet again, someone would select Revision B in H3, and the entire sheet would reset to black, making any changes in Revision A black, and now the font is red again allowing us to note changes specifically from Revision B.
This would continue to Revision J.

There are 5 different sheets that I need the formatting across, all based on the selection from H3 in sheet1.

What else do I need to let you know?

Thanks so so so much, hopefully we can figure this out.

 

[jmarting13]

I don't want to waste too much of your time, but I'm 100% open to any changed you'd suggest.

In a perfect world how the sheet would work is:
When the sheet is filled out with Quote or RFI selected in cell H3, nothing is special, and the font is black.
If we need to revise the sheet, someone selects Revision A in H3, and the font now turns to red, marking any changes and allowing our administration to see the changes.
If we need to revise the sheet again, someone would select Revision B in H3, and the entire sheet would reset to black, making any changes in Revision A black, and now the font is red again allowing us to note changes specifically from Revision B.
This would continue to Revision J.

There are 5 different sheets that I need the formatting across, all based on the selection from H3 in sheet1.

What else do I need to let you know?

Thanks so so so much, hopefully we can figure this out.

Whoops... I thought the first one didn't post so I kinda accidentally said the same thing twice, but I guess it might help with clarity.

 

[Joe4]

I think I may have an idea, but I need a few more details.

What is the name of the sheet with the H3 that you are changing?
What is the name of the other 5 sheets that you want to apply this logic too?
Are there any other sheets other than these 6 in your workbook?

 

[jmarting13]

1. SPAC RFQ (1)
2. SPAC RFQ (2), SPAC RFQ (3), SPAC RFQ (4), SPAC RFQ (5)
3. New Customer Info, DATA (DO NOT REMOVE)

 

[Joe4]

So, should the font color change only affect the following sheets: SPAC RFQ (2), SPAC RFQ (3), SPAC RFQ (4), SPAC RFQ (5)
or does it also apply to the SPAC RFQ (1) sheet?

 

[jmarting13]

It also applies to SPAC RFQ (1)

 

[Joe4]

OK, I think I might a solution that does what you want. You are going to have to put "Worksheet_Change" event procedure code on all five worksheets.

First, place this one in the "SPAC RFQ (1)" sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim shts()
    Dim i As Long

'   Check to see if Range("H3") is the updated cell
    If Not Intersect(Target, Range("H3")) Is Nothing Then
  
'       Set array of sheet names
        shts = Array("SPAC RFQ (1)", "SPAC RFQ (2)", "SPAC RFQ (3)", "SPAC RFQ (4)", "SPAC RFQ (5)")
  
'       Make all cells on all sheets black whenever cell H3 is updated
'       Loop through other sheets and change font to black
        For i = LBound(shts) To UBound(shts)
            Sheets(shts(i)).Cells.Font.Color = vbBlack
        Next i
      
'   For changes to any other cells
    Else
'       If cell H3 contains revision, turned all changed cells red
        If Left(Range("H3"), 8) = "Revision" Then
'           Change text just entered to red
            Target.Font.Color = vbRed
        End If
    End If
      
End Sub

Then, place this code on each of the other 4 "SPAC RFQ..." sheet modules:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if value in H3 on first sheet starts with "Revision"
    If Left(Sheets("SPAC RFQ (1)").Range("H3"), 8) = "Revision" Then
'       Change text just entered to red
        Target.Font.Color = vbRed
    End If
  
End Sub

Test it out and let me know if that does what you want.

 

[jmarting13]

OK, I think I might a solution that does what you want. You are going to have to put "Worksheet_Change" event procedure code on all five worksheets.

First, place this one in the "SPAC RFQ (1)" sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim shts()
    Dim i As Long

'   Check to see if Range("H3") is the updated cell
    If Not Intersect(Target, Range("H3")) Is Nothing Then
 
'       Set array of sheet names
        shts = Array("SPAC RFQ (1)", "SPAC RFQ (2)", "SPAC RFQ (3)", "SPAC RFQ (4)", "SPAC RFQ (5)")
 
'       Make all cells on all sheets black whenever cell H3 is updated
'       Loop through other sheets and change font to black
        For i = LBound(shts) To UBound(shts)
            Sheets(shts(i)).Cells.Font.Color = vbBlack
        Next i
     
'   For changes to any other cells
    Else
'       If cell H3 contains revision, turned all changed cells red
        If Left(Range("H3"), 8) = "Revision" Then
'           Change text just entered to red
            Target.Font.Color = vbRed
        End If
    End If
     
End Sub

Then, place this code on each of the other 4 "SPAC RFQ..." sheet modules:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if value in H3 on first sheet starts with "Revision"
    If Left(Sheets("SPAC RFQ (1)").Range("H3"), 8) = "Revision" Then
'       Change text just entered to red
        Target.Font.Color = vbRed
    End If
 
End Sub

Test it out and let me know if that does what you want.

Wow. You did it. It works PERFECTLY. Thank you so much. I've spent hours upon hours trying to figure this out.

 

[Joe4]

You are welcome.

Yeah, it can get a little tricky when dealing with values being changed manually, values by formula, and controlling other sheets with Worksheet_Change event procedures.
Glad we were able to figure something out that works for you!