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?

 

[Joe4]

"Target" is not a default range variable in "Worksheet_Calculate" event procedures. It is found in "Worksheet_Change" and "Worksheet_SelectionChange" event procedures, where you are able to tie the change to a particular cell or range of cells. "Worksheet_Calculate" cannot identify what cell changed.

So you need to use explicit range references in that code. You could use "Xrg", since you defined that yourself.

 

[jmarting13]

"Target" is not a default range variable in "Worksheet_Calculate" event procedures. It is found in "Worksheet_Change" and "Worksheet_SelectionChange" event procedures, where you are able to tie the change to a particular cell or range of cells. "Worksheet_Calculate" cannot identify what cell changed.

So you need to use explicit range references in that code. You could use "Xrg", since you defined that yourself.

Ok. I'm really not the best at VBA coding and am kind of lost. Could you look at the code I attached and let me know what part I should change? Thanks!!

 

[Joe4]

What code we want to use depends on some things.

When do we want this code to run?
- When the file is first opened?
- When the value in cell K1 changes?

How is the value in K1 updated?
- Is it manually updated?
- Is it a formula or link?

 

[jmarting13]

What code we want to use depends on some things.

When do we want this code to run?
- When the file is first opened?
- When the value in cell K1 changes?

How is the value in K1 updated?
- Is it manually updated?
- Is it a formula or link?

Thanks for the speedy reply! I've been trying to get this issue fixed for a while, and you seem like you actually might be able to help . I want the code to run when the value of K1 changes, which is a formula of ='SPAC RFQ (1)'!H3. It's basically just equal to another cell. So whenever cell H3 on the other sheet changes, I want the code to run.

 

[Joe4]

OK, then you would just use something like this:

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

or

Private Sub Worksheet_Calculate()
    Dim Xrg As Range
    Set Xrg = Range("K1")
    If Xrg.Value = "Quote" Then
        Xrg.Font.Color = vbBlack
    Else
        Xrg.Font.Color = vbRed
    End If
End Sub

if you prefer.

 

[jmarting13]

OK, then you would just use something like this:

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

The problem with that is that I want the default font to change for the entire page. Basically once the code runs, anything I type from there on forward would be in red, rather than just changing a cell color to red.

 

[Joe4]

The problem with that is that I want the default font to change for the entire page. Basically once the code runs, anything I type from there on forward would be in red, rather than just changing a cell color to red.

OK, that part was not clear to me from what you have told us so far.

Try this:

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

 

[jmarting13]

OK, that part was not clear to me from what you have told us so far.

Try this:

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

It's all good, I just forgot to specify that.
When I put in your code, it just changed all the cells to red. My main goal with this code is to change only the future changes to red.
Basically to fill you in a little bit more, my sheet has a revision box where if they select a revision, I want the entire sheet to change the font to black, then anything they change on the sheet turns to red so it's noticeable that they altered something. It works just fine on my main sheet, but once I try and bring it over to the second sheet it no longer works.

On sheet 1 the code that works is this. I know its really messy and probably not the best code, but it works. Now I need this on the other sheets.
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

Thanks so much for your help and patience!!

 

[Joe4]

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).