VBA to change black automatic font color for future entries

[data808]

Does anyone know how to change the default black automatic font color using VBA? I would like to do this so that the user can type in all future cells with the color red as the font color until they decide to change it back to black. Could not find anything online to do this simple yet seemingly impossible task. I hope someone out there knows how to do this or at least a workaround. Thank you in advance.

 

[bebo021999]

For example, change defaul font color to red, as activate sheet
This must be placed in ThisWorkbook module:
(Right click sheet nam/ view code/ ThisWorkbook, paste below code into:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Cells.Font.Color = vbRed
End Sub

But, after manual changing color (red to black), next time sheet activate, all font color will be set back to red
With VBA code, it performs a hard work, by looping each cell with format.

Therefore, try to use Style Option, and change the seting with font colour as red.

 

[data808]

For example, change defaul font color to red, as activate sheet
This must be placed in ThisWorkbook module:
(Right click sheet nam/ view code/ ThisWorkbook, paste below code into:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Cells.Font.Color = vbRed
End Sub

But, after manual changing color (red to black), next time sheet activate, all font color will be set back to red
With VBA code, it performs a hard work, by looping each cell with format.

Therefore, try to use Style Option, and change the seting with font colour as red.

View attachment 74917

Thanks for your suggestions. I'm still working on the first part with the VBA. I put the code into the workbook change event like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("O1").Locked = False Then
    Sh.Cells.Font.Color = vbRed
Else
    Sh.Cells.Font.Color = vbBlack
End If

End Sub

I get an error message that says object required and it will point to either the vbRed line or the vbBlack line depending on if the O1 cell is locked or unlocked. So at least the button is working as it should. The button basically locks/unlocks cell O1 so I based the font color changing on that.

 

[data808]

For example, change defaul font color to red, as activate sheet
This must be placed in ThisWorkbook module:
(Right click sheet nam/ view code/ ThisWorkbook, paste below code into:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Cells.Font.Color = vbRed
End Sub

But, after manual changing color (red to black), next time sheet activate, all font color will be set back to red
With VBA code, it performs a hard work, by looping each cell with format.

Therefore, try to use Style Option, and change the seting with font colour as red.

View attachment 74917

For example, change defaul font color to red, as activate sheet
This must be placed in ThisWorkbook module:
(Right click sheet nam/ view code/ ThisWorkbook, paste below code into:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Cells.Font.Color = vbRed
End Sub

But, after manual changing color (red to black), next time sheet activate, all font color will be set back to red
With VBA code, it performs a hard work, by looping each cell with format.

Therefore, try to use Style Option, and change the seting with font colour as red.

View attachment 74917

Ok I tried your VBA code exactly how you instructed. I put it into the workbook activesheet event and nothing happens when I type into random cells. No red font. Everything is black font.

Now I'm going to try your second suggestion and I'll let you know what happens. Thanks.

 

[data808]

For example, change defaul font color to red, as activate sheet
This must be placed in ThisWorkbook module:
(Right click sheet nam/ view code/ ThisWorkbook, paste below code into:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Cells.Font.Color = vbRed
End Sub

But, after manual changing color (red to black), next time sheet activate, all font color will be set back to red
With VBA code, it performs a hard work, by looping each cell with format.

Therefore, try to use Style Option, and change the seting with font colour as red.

View attachment 74917

Ok I just tried your second suggestion and that would have been a great solution except once I format the "Normal" setting with the color I want, it will change all cell values in the spreadsheet with that font color. I need it to only change future entries while leaving alone the values that were previously entered.

 

[data808]

Apologies @Joe4 and thank you for your help on this. I am in the middle of doing what you suggested from the other post that was closed and am still getting the application-defined or object-defined error and then it points to either this line cell.Font.Color = -16776961 or this line cell.Font.ColorIndex = xlAutomatic depending on if the button is set for red or black font color. Would you know why this is happening? I usually do test the code in a fresh blank spreadsheet just to make sure its working before I apply it to the spreadsheet I need it to work in. I know you mentioned it worked flawless for you but not sure what I am doing wrong. Here is what I have in the Sheet1 change event:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range

' See if any cells updates in watched range
Set rng = Intersect(Target, Range("A1:C10"))
If rng Is Nothing Then Exit Sub

' Loop through newly updated cells in watched range
For Each cell In rng
If Range("Z1") = "Red" Then
cell.Font.Color = -16776961
Else
cell.Font.ColorIndex = xlAutomatic
End If
Next cell

End Sub

Then this is what I have assigned to the button:

Sub font_color()

If Range("Z1") = "Black" Then
Range("Z1") = "Red"
Else
Range("Z1") = "Black"
End If

ActiveSheet.Unprotect "hello"
ActiveSheet.Shapes.Range(Array("Button 1")).Select
If Selection.Characters.Text = "BLACK" Then
With Selection.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
'.TintAndShade = 0
'.ThemeFont = xlThemeFontMinor
End With
With Selection.Font
.Color = -16776961
'.TintAndShade = 0
End With
Selection.Characters.Text = "RED"
With Selection.Characters(Start:=1, Length:=3).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
ActiveSheet.Protect "hello"
Exit Sub
End If

ActiveSheet.Unprotect "hello"
ActiveSheet.Shapes.Range(Array("Button 1")).Select
If Selection.Characters.Text = "RED" Then
With Selection.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
'.TintAndShade = 0
'.ThemeFont = xlThemeFontMinor
End With
With Selection.Font
.ColorIndex = xlAutomatic
'.TintAndShade = 0
End With
Selection.Characters.Text = "BLACK"
With Selection.Characters(Start:=1, Length:=5).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveSheet.Protect "hello"
End If

End Sub

Another odd thing that I found is that I could not rename the button to another name. I wanted to change it from Button 1 to font_color but it kept reverting back and so I adjusted the code to show Button 1 as the name of the button. The button seems to function as far as holding the word "Red" or "Black" in the Z1 cell and also changing the word on the button itself from "RED" to "BLACK" but thats about it. I just never had a problem renaming a button before. Every time I try it just pops the VBA window and shows the code for the button.

 

[Joe4]

Apologies @Joe4 and thank you for your help on this. I am in the middle of doing what you suggested from the other post that was closed and am still getting the application-defined or object-defined error and then it points to either this line cell.Font.Color = -16776961 or this line cell.Font.ColorIndex = xlAutomatic depending on if the button is set for red or black font color. Would you know why this is happening? I usually do test the code in a fresh blank spreadsheet just to make sure its working before I apply it to the spreadsheet I need it to work in. I know you mentioned it worked flawless for you but not sure what I am doing wrong.

A few things to check:
- Are you really using Excel 2019 for this? What is the extension of the file?
- Do you know any global variables declared anywhere, or any other procedures or functions?
Specifically, I am looking for anything you may have created with the names "cell", "Font", or "ColorIndex". Doing so may cause confusion and unexpected errors like this.
- If all else fails, try this on a brand new Excel sheet. It is possible that your workbook has become corrupted.

 

[data808]

A few things to check:
- Are you really using Excel 2019 for this? What is the extension of the file?
- Do you know any global variables declared anywhere, or any other procedures or functions?
Specifically, I am looking for anything you may have created with the names "cell", "Font", or "ColorIndex". Doing so may cause confusion and unexpected errors like this.
- If all else fails, try this on a brand new Excel sheet. It is possible that your workbook has become corrupted.

Thanks @Joe4. Yes I just checked again and I am using MS Office 2019 Professional Plus. The extension of the file is a macro-enabled type ending with .xlsm.

I don't think I have any global variables declared anywhere as this is a brand new blank spreadsheet and so I only have codes in Sheet1 for the Worksheet Change event and then there is the module 1 for the button which is under (General) font_color.

If there is a way to upload the spreadsheet so you can see the file itself, let me know.

Also, I just figured out why I couldn't rename the button. It's because of the underscore that I was trying to use in the name "font_color". So now that I tried without the underscore "Font Color" it saves just fine with that name.

 

[data808]

A few things to check:
- Are you really using Excel 2019 for this? What is the extension of the file?
- Do you know any global variables declared anywhere, or any other procedures or functions?
Specifically, I am looking for anything you may have created with the names "cell", "Font", or "ColorIndex". Doing so may cause confusion and unexpected errors like this.
- If all else fails, try this on a brand new Excel sheet. It is possible that your workbook has become corrupted.

I've uploaded some images for you. Not sure if that will help.

Red and black click is what the sheet looks like when I click the button. It will go between red or black. Then if I type into range A1:C10 the error will come up. Then if I click the debug, it will show whats in the debug picture. Then the last picture is just to show you what code I have for the button. It's cut off but I think you get the idea.

 

[Joe4]

I have no idea why it doesn't work for you.
If you want to upload the file to a file sharing site and provide a link to it in this thread, I will take a look at it when I have a chance.