Defining a range

jmarting13

New Member
Joined
Mar 2, 2021
Messages
27
Platform
  1. Windows
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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
"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

New Member
Joined
Mar 2, 2021
Messages
27
Platform
  1. Windows
"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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 2, 2021
Messages
27
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
OK, then you would just use something like this:
VBA Code:
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
VBA Code:
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

New Member
Joined
Mar 2, 2021
Messages
27
Platform
  1. Windows

ADVERTISEMENT

OK, then you would just use something like this:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
Private Sub Worksheet_Calculate()
    If Range("K1").Value = "Quote" Then
        Cells.Font.Color = vbBlack
    Else
        Cells.Font.Color = vbRed
    End If
End Sub
 

jmarting13

New Member
Joined
Mar 2, 2021
Messages
27
Platform
  1. Windows
OK, that part was not clear to me from what you have told us so far.

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

1617292533542.png

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,129,578
Messages
5,637,204
Members
416,960
Latest member
Carbon1198

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
Top