VBA for A15 cell to calculate red font values and C15 cell to calculate black font values

[data808]

I have a spreadsheet that users will type number values into cell range A1:C10. Then A15 and C15 will tally up the totals of these values but is there a way for cell A15 to only tally up if the font color is red within the cells of A1:C10? Then have C15 cell only tally up if the font color is black within the same cell range A1:C10?

Thanks.

 

[Joe4]

How is the font color being set?
Manually, by Custom Formatting, or by Conditional Formatting?

 

[data808]

How is the font color being set?
Manually, by Custom Formatting, or by Conditional Formatting?

Thanks. While we are on the subject, I actually was thinking I could take care of the color of the font no problem with a button and some VBA but it appears that excel does not allow for the user to easily change the automatic black font color to red so that the user can type in red indefinitely until they want to change back to black. So I was wondering if you knew how to change the automatic color from black to red?

Basically what I want is for the user to be able to switch from red to black font colors and stay that color with the click of a button. The only time the color would change back is when they click the button again. From there they enter number values into the cells based on the color they selected from the button that they click. Lastly, I would like to have one cell add up all the red font colors and another cell add up all the black font colors. I have the code I worked on so far and it basically only changes the text on the button itself from red to black. Here is the code so far:

Sub Red_Black_Font()
'
' Red_Black_Font Macro
' switches font color from red to black

ActiveSheet.Unprotect "hello"
ActiveSheet.Shapes.Range(Array("Red Black Font")).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("Red Black Font")).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

Oh btw, I deactivated the tint lines because it causes an error for some reason.

 

[Joe4]

I wonder if there might be a more effective way to accomplish what you are trying to do.
There are various issues/obstacles with your current plan of action:

1. Native Excel formulas only work against cell values, they cannot run against cell formatting. So if you wanted to do something against font coloring that was applied manually (and not be Conditional Formatting or Custom Formatting), I think you will need to do this in VBA.

2. When setting the font color, you usually do it per cell (i.e. it is applied to some pre-defined cell). I do not know if you can toggle the color, and have it set all new future entries anywhere in some range to be a certain color. Well, at least not directly. You may be able to have the button toggle some value in some hidden cell, and then have a Worksheet_Change event procedure code in VBA automatically change the color of a new cell entry based on the value in that hidden cell. It is an indirect method, but should work in theory.

So, while it may all be possible, we have to ask ourselves if we are unnecessarily complicating the task.
Can you show us some sample data, and explain why you want to do it this way?
We may be able to provide other alternatives, if we have a better understanding of the end goal and why you want to do it this way.

 

[data808]

I wonder if there might be a more effective way to accomplish what you are trying to do.
There are various issues/obstacles with your current plan of action:

1. Native Excel formulas only work against cell values, they cannot run against cell formatting. So if you wanted to do something against font coloring that was applied manually (and not be Conditional Formatting or Custom Formatting), I think you will need to do this in VBA.

2. When setting the font color, you usually do it per cell (i.e. it is applied to some pre-defined cell). I do not know if you can toggle the color, and have it set all new future entries anywhere in some range to be a certain color. Well, at least not directly. You may be able to have the button toggle some value in some hidden cell, and then have a Worksheet_Change event procedure code in VBA automatically change the color of a new cell entry based on the value in that hidden cell. It is an indirect method, but should work in theory.

So, while it may all be possible, we have to ask ourselves if we are unnecessarily complicating the task.
Can you show us some sample data, and explain why you want to do it this way?
We may be able to provide other alternatives, if we have a better understanding of the end goal and why you want to do it this way.

That is a great answer and thank you for responding. Yes I would like to keep away from conditional formatting and custom formatting as it seems limited to what I want to do. I also understand the limitations of excel not being able to set a color other than black for automatic which makes all future entries the color you would want. That makes total sense to do a hidden cell locked/unlocked and use that as a switch in the VBA if statement to check for that and for example if locked then use red font on future entries and if unlocked use black for future entries. I think that may be the best method.

As for why I am doing this, you are correct and may be able to come up with another solution to the scenario I am having with the data. We are logging what documents are received each day. There are multiple types of documents and so they will need to be categorized. On a rare occasion these documents are received late within the day and we are not able to get to processing them all. In those cases we use red font color to show that these documents were received yesterday but we processed them the next day and so they are included into the next day's processing count. And so to make matters even more complex, these late processed documents are logged in red color font in one cell and in that same cell, there may be also be a black font number to represent the number of documents that were processed on time. This spreadsheet at the moment is pretty much maxed out as far as space goes for printing and so there isn't much room left to be making separate cells just for the rare occurrence of these late processed documents. I don't think it is justified to keep separate cells just for when that happens. So the ranges I gave (A1:C10) for where the data will be entered was just to make it small and simple for me to get the general idea before I apply it to the vast source sheet that I need this all to work in. The actual spreadsheet is much much larger. lol

 

[Joe4]

OK, then try attaching this VBA code to the button you are using to toggle font colors:

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

Note that I am using cell Z1 to store the color, but you can change it to whatever cell you want.

Then, put this code in the "Sheet" module of the sheet you want it to run against:

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

Once again, you can change your ranges to suit your needs, but this works on the example you presented.

So this will handle your font coloring needs.
As for the sum, lots of people have created custom VBA functions to sum by color (just Google "Excel Sum By Color", and you should find lots of threads on this topic).
Note that many may deal with the background color, so you might need to tweak the code to look at the font color instead of the background color (should be a very minor tweak).

 

[data808]

OK, then try attaching this VBA code to the button you are using to toggle font colors:

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

Note that I am using cell Z1 to store the color, but you can change it to whatever cell you want.

Then, put this code in the "Sheet" module of the sheet you want it to run against:

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

Once again, you can change your ranges to suit your needs, but this works on the example you presented.

So this will handle your font coloring needs.
As for the sum, lots of people have created custom VBA functions to sum by color (just Google "Excel Sum By Color", and you should find lots of threads on this topic).
Note that many may deal with the background color, so you might need to tweak the code to look at the font color instead of the background color (should be a very minor tweak).

Thank you very much for the help. Sorry I haven't had time yet to test this out but I will hopefully try to get to it today and let you know.

 

[data808]

OK, then try attaching this VBA code to the button you are using to toggle font colors:

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

Note that I am using cell Z1 to store the color, but you can change it to whatever cell you want.

Then, put this code in the "Sheet" module of the sheet you want it to run against:

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

Once again, you can change your ranges to suit your needs, but this works on the example you presented.

So this will handle your font coloring needs.
As for the sum, lots of people have created custom VBA functions to sum by color (just Google "Excel Sum By Color", and you should find lots of threads on this topic).
Note that many may deal with the background color, so you might need to tweak the code to look at the font color instead of the background color (should be a very minor tweak).

Ok I did some testing and tweaked the code a bit. I changed the range from A1:C10 to A1:F10 so I have more room to play with. Then I also decided to change the switch from your Z1 color idea to make tied to the button instead. Initially the button switches between "RED" or "BLACK" as the word written on the button itself. So I made the button lock O1 cell if the button says "BLACK" on it. If the button says "RED" on it, then it will unlock O1 cell. After that I wrote this 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("O1").Locked = False Then
            cell.Font.Color = -16776961
        Else
            cell.Font.ColorIndex = xlAutomatic
        End If
    Next cell

End Sub

The button is working and locked or unlocking the cell as it should but once I type something in A1:F10 range it will give this error:

Run-time error '1004':

Application-defined or object-defined error

Then it will point to this line:

cell.Font.Color = -16776961

OR

cell.Font.ColorIndex = xlAutomatic

Depending on which color the code is on based on the locked/unlocked O1 cell.

 

[Joe4]

Can you turn on your Macro Recorder and record yourself changing the font color on a cell from black-to-red and then back, from red-to-black, and then turn off the Macro Recorder?
Then, inspect the code, and see which color codes it is using.
If you have an issue deciphering the recorded code, please copy and paste it here.

 

[data808]

Can you turn on your Macro Recorder and record yourself changing the font color on a cell from black-to-red and then back, from red-to-black, and then turn off the Macro Recorder?
Then, inspect the code, and see which color codes it is using.
If you have an issue deciphering the recorded code, please copy and paste it here.

That red color code -16776961 is from recording a macro. I gave that to you first in post #3. I would never be able to guess that color code myself. Lol. So because that is the red color code for the text on the button itself and not for an actual cell font color, that could be why we are getting that error. The color codes are probably different for font colors in cells. Do you know the color code for the font colors in cells?