Sum by font color lags or doesn't stick with respective color

D

data808

Active Member
Joined
Dec 3, 2010
Messages
353
Office Version
  1. 2019
Platform
  1. Windows
I have a template that the user will enter numeric data into cell ranges A1:C10. Numbers will usually range from 1-500. Some cells will also be blank if no data is given to record. Some of the values will be typed in red font color and some will be in black font color. The user may change the data randomly while they use it along with also changing the color of the font on the fly. I have a button for the user that can change the font color between red and black as this spreadsheet is also locked by the traditional method of changing font colors in the ribbon. Here is the code I am using in a module to sum by font color:

VBA Code: 
Public Function sumRed(r As Range)
Dim ce As Range
sumRed = 0
For Each ce In r
    If ce.Font.ColorIndex = 3 Then sumRed = sumRed + ce.Value
Next ce
End Function

Public Function sumBlack(r As Range)
Dim ce As Range
sumBlack = 0
For Each ce In r
    If ce.Font.ColorIndex = 1 Then sumBlack = sumBlack + ce.Value
Next ce
End Function

Then I have these in cells A15 and C15 respectively:
=sumRed(A1:C10)
=sumBlack(A1:C10)

The problem that seems to be occurring is that the calculation in these cells don't seem to be on the fly or "live" I should say. It is being triggered by some odd events. Like if a cell was set to red because the user had previously typed red font into that cell and now wants to clear it and then change it to black font, it won't add it in C15 for black. It will add it into cell A15 for red because it was originally red. It's as if the calculation is happening before the font changes color. Is there a way to make this method of summing by font color more accurate?

Thanks for the help.
 
Sort by date Sort by votes
Ok thanks... will play around... In the meantime try this code in Worksheet Event. You will see that it has change from a Change to Selection Change event... (update... got it working to change the text color in black to red and red to black.. also now when I click in the range it changes the font color as well from red to black and black to red... is this what you were after?)

VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Calculate

' checks if cell AZ1 is unlocked. if unlocked then font color is red. if locked then font _
color is black
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
ActiveSheet.Unprotect "hello"
For Each cell In rng
If Range("O1").Locked = False Then
cell.Font.ColorIndex = 3
Else
cell.Font.ColorIndex = xlAutomatic
End If
Next cell
ActiveSheet.Protect "hello"
End Sub
 
Last edited:
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
UPDATE... @data808

Ok... The following works for me and I hope it works the way that you would want... Please test on a sample of your data first...

This code on the worksheet event:

VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Calculate
    ' checks if cell AZ1 is unlocked. if unlocked then font color is red. if locked then font _
    color is black
    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
    ActiveSheet.Unprotect "hello"
    For Each cell In rng
        If Range("O1").Locked = False Then
            Calculate
            cell.Font.ColorIndex = 3
        Else
            Calculate
            cell.Font.ColorIndex = 1
        End If
    Next cell
    Calculate
    ActiveSheet.Protect "hello"
End Sub


Module 1

VBA Code: 
Option Explicit
Sub Red_Black_Font()
    ' Red_Black_Font Macro
    ' switches font color from red to black
    ActiveSheet.Unprotect "hello"
    Range("O1").Locked = False
    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
        ActiveCell.Font.ColorIndex = 3
        Calculate
        ActiveSheet.Protect "hello"
        Exit Sub
    End If
    ActiveSheet.Unprotect "hello"
    Range("O1").Locked = True
    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
        ActiveCell.Font.ColorIndex = 1
        Calculate
        ActiveSheet.Protect "hello"
    End If
End Sub

Module 2

VBA Code: 
Option Explicit
Public Function sumRed(r As Range)
Application.Volatile
    Dim ce As Range
    sumRed = 0
    For Each ce In r
        If ce.Font.ColorIndex = 3 Then sumRed = sumRed + ce.Value
    Next ce
End Function
Public Function sumBlack(r As Range)
Application.Volatile
    Dim ce As Range
    sumBlack = 0
    For Each ce In r
        If ce.Font.ColorIndex = 1 Then sumBlack = sumBlack + ce.Value
    Next ce
End Function

Formula's to use

Excel Formula: 
=IF(NOW()>0,sumRed(A1:C10))
=IF(NOW()>0,sumBlack(A1:C10))
 
Last edited:
Upvote 0
Jimmypop said:
Ok thanks... will play around... In the meantime try this code in Worksheet Event. You will see that it has change from a Change to Selection Change event... (update... got it working to change the text color in black to red and red to black.. also now when I click in the range it changes the font color as well from red to black and black to red... is this what you were after?)

VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Calculate

' checks if cell AZ1 is unlocked. if unlocked then font color is red. if locked then font _
color is black
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
ActiveSheet.Unprotect "hello"
For Each cell In rng
If Range("O1").Locked = False Then
cell.Font.ColorIndex = 3
Else
cell.Font.ColorIndex = xlAutomatic
End If
Next cell
ActiveSheet.Protect "hello"
End Sub
Click to expand...
Thanks. I can try it but before I do I want to make sure we are on the same page. I have thought about the selection change event for when the cursor moves between cells it can trigger the font colors which will cause the A15 and C15 to auto calculate more frequently and of course after the font color has already changed when I press enter which is good, however, does this mean all the cells with previously entered values will also change colors based on the whatever color the button is reflecting? If so, that wouldn't work. Sorry forgot to mention that based on the button, when the user clicks this button to change font colors, I only wanted future entries to be affected, not previous ones. Does that make sense? Sorry for the confusion.
 
Upvote 0
Jimmypop said:
UPDATE... @data808

Ok... The following works for me and I hope it works the way that you would want... Please test on a sample of your data first...

This code on the worksheet event:

VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Calculate
    ' checks if cell AZ1 is unlocked. if unlocked then font color is red. if locked then font _
    color is black
    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
    ActiveSheet.Unprotect "hello"
    For Each cell In rng
        If Range("O1").Locked = False Then
            Calculate
            cell.Font.ColorIndex = 3
        Else
            Calculate
            cell.Font.ColorIndex = 1
        End If
    Next cell
    Calculate
    ActiveSheet.Protect "hello"
End Sub


Module 1

VBA Code: 
Option Explicit
Sub Red_Black_Font()
    ' Red_Black_Font Macro
    ' switches font color from red to black
    ActiveSheet.Unprotect "hello"
    Range("O1").Locked = False
    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
        ActiveCell.Font.ColorIndex = 3
        Calculate
        ActiveSheet.Protect "hello"
        Exit Sub
    End If
    ActiveSheet.Unprotect "hello"
    Range("O1").Locked = True
    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
        ActiveCell.Font.ColorIndex = 1
        Calculate
        ActiveSheet.Protect "hello"
    End If
End Sub

Module 2

VBA Code: 
Option Explicit
Public Function sumRed(r As Range)
Application.Volatile
    Dim ce As Range
    sumRed = 0
    For Each ce In r
        If ce.Font.ColorIndex = 3 Then sumRed = sumRed + ce.Value
    Next ce
End Function
Public Function sumBlack(r As Range)
Application.Volatile
    Dim ce As Range
    sumBlack = 0
    For Each ce In r
        If ce.Font.ColorIndex = 1 Then sumBlack = sumBlack + ce.Value
    Next ce
End Function

Formula's to use

Excel Formula: 
=IF(NOW()>0,sumRed(A1:C10))
=IF(NOW()>0,sumBlack(A1:C10))
Click to expand...
Ok I tested this and the font color follows the button even for previous values if I make the cursor go over the values it will change color. Example: If I type all red values first, then click the button to change to black and move my cursor over those existing values that I previously typed in red, it will start to change those to black. However, the sum calculating seems to be more accurate now as it feels like its live as the fonts are changing color, the totals are readjusting which is what I want for that. Just need to figure out how to make it so that only future entries are affected by the color of the button as the user changes back and forth between the 2 colors. Thank you very much for the help on this. I feel like we are inching closer to the goal.
 
Upvote 0
Hi

Ok lets change back to a Change Event and not Selection Change... I have tested and it works like this now...

Start... All Black Text Total 3000

1.png

Font changed to Red (No cells affected so far)

2.png

A1 changed and enter was pressed... On pressing enter font changed to red and total was updated to 100 and 2900. Font color change will only happen after cell is updated and enter is pressed and not with just selecting it. It needs an update to happen... When you start typing and previous font was black, it will stay black when starting to type but will update on enter pressed. Totals will only be affected once enter is pressed... I hope this is what you would require... :cool:

3.png

Sheet Code:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Calculate
'     checks if cell AZ1 is unlocked. if unlocked then font color is red. if locked then font _
    color is black
    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
    ActiveSheet.Unprotect "hello"
    For Each cell In rng
        If Range("O1").Locked = False Then
            Calculate
            cell.Font.ColorIndex = 3
        Else
            Calculate
            cell.Font.ColorIndex = 1
        End If
    Next cell
    Calculate
    ActiveSheet.Protect "hello"
End Sub

Module 1

VBA Code: 
Option Explicit
Sub Red_Black_Font()
'     Red_Black_Font Macro
'     switches font color from red to black
    ActiveSheet.Unprotect "hello"
    Range("O1").Locked = False
    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
        Calculate
        ActiveSheet.Protect "hello"
        Exit Sub
    End If
    ActiveSheet.Unprotect "hello"
    Range("O1").Locked = True
    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
        Calculate
        ActiveSheet.Protect "hello"
    End If
End Sub

Module 2

VBA Code: 
Option Explicit
Public Function sumRed(r As Range)
Application.Volatile
    Dim ce As Range
    sumRed = 0
    For Each ce In r
        If ce.Font.ColorIndex = 3 Then sumRed = sumRed + ce.Value
    Next ce
End Function
Public Function sumBlack(r As Range)
Application.Volatile
    Dim ce As Range
    sumBlack = 0
    For Each ce In r
        If ce.Font.ColorIndex = 1 Then sumBlack = sumBlack + ce.Value
    Next ce
End Function
 
Upvote 0
Solution
Jimmypop said:
Hi

Ok lets change back to a Change Event and not Selection Change... I have tested and it works like this now...

Start... All Black Text Total 3000

View attachment 76205

Font changed to Red (No cells affected so far)

View attachment 76206

A1 changed and enter was pressed... On pressing enter font changed to red and total was updated to 100 and 2900. Font color change will only happen after cell is updated and enter is pressed and not with just selecting it. It needs an update to happen... When you start typing and previous font was black, it will stay black when starting to type but will update on enter pressed. Totals will only be affected once enter is pressed... I hope this is what you would require... :cool:

View attachment 76207

Sheet Code:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Calculate
'     checks if cell AZ1 is unlocked. if unlocked then font color is red. if locked then font _
    color is black
    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
    ActiveSheet.Unprotect "hello"
    For Each cell In rng
        If Range("O1").Locked = False Then
            Calculate
            cell.Font.ColorIndex = 3
        Else
            Calculate
            cell.Font.ColorIndex = 1
        End If
    Next cell
    Calculate
    ActiveSheet.Protect "hello"
End Sub

Module 1

VBA Code: 
Option Explicit
Sub Red_Black_Font()
'     Red_Black_Font Macro
'     switches font color from red to black
    ActiveSheet.Unprotect "hello"
    Range("O1").Locked = False
    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
        Calculate
        ActiveSheet.Protect "hello"
        Exit Sub
    End If
    ActiveSheet.Unprotect "hello"
    Range("O1").Locked = True
    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
        Calculate
        ActiveSheet.Protect "hello"
    End If
End Sub

Module 2

VBA Code: 
Option Explicit
Public Function sumRed(r As Range)
Application.Volatile
    Dim ce As Range
    sumRed = 0
    For Each ce In r
        If ce.Font.ColorIndex = 3 Then sumRed = sumRed + ce.Value
    Next ce
End Function
Public Function sumBlack(r As Range)
Application.Volatile
    Dim ce As Range
    sumBlack = 0
    For Each ce In r
        If ce.Font.ColorIndex = 1 Then sumBlack = sumBlack + ce.Value
    Next ce
End Function
Click to expand...
Ok I'm going to test this out now. I'm excited. lol
 
Upvote 0
Jimmypop said:
Hi

Ok lets change back to a Change Event and not Selection Change... I have tested and it works like this now...

Start... All Black Text Total 3000

View attachment 76205

Font changed to Red (No cells affected so far)

View attachment 76206

A1 changed and enter was pressed... On pressing enter font changed to red and total was updated to 100 and 2900. Font color change will only happen after cell is updated and enter is pressed and not with just selecting it. It needs an update to happen... When you start typing and previous font was black, it will stay black when starting to type but will update on enter pressed. Totals will only be affected once enter is pressed... I hope this is what you would require... :cool:

View attachment 76207

Sheet Code:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Calculate
'     checks if cell AZ1 is unlocked. if unlocked then font color is red. if locked then font _
    color is black
    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
    ActiveSheet.Unprotect "hello"
    For Each cell In rng
        If Range("O1").Locked = False Then
            Calculate
            cell.Font.ColorIndex = 3
        Else
            Calculate
            cell.Font.ColorIndex = 1
        End If
    Next cell
    Calculate
    ActiveSheet.Protect "hello"
End Sub

Module 1

VBA Code: 
Option Explicit
Sub Red_Black_Font()
'     Red_Black_Font Macro
'     switches font color from red to black
    ActiveSheet.Unprotect "hello"
    Range("O1").Locked = False
    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
        Calculate
        ActiveSheet.Protect "hello"
        Exit Sub
    End If
    ActiveSheet.Unprotect "hello"
    Range("O1").Locked = True
    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
        Calculate
        ActiveSheet.Protect "hello"
    End If
End Sub

Module 2

VBA Code: 
Option Explicit
Public Function sumRed(r As Range)
Application.Volatile
    Dim ce As Range
    sumRed = 0
    For Each ce In r
        If ce.Font.ColorIndex = 3 Then sumRed = sumRed + ce.Value
    Next ce
End Function
Public Function sumBlack(r As Range)
Application.Volatile
    Dim ce As Range
    sumBlack = 0
    For Each ce In r
        If ce.Font.ColorIndex = 1 Then sumBlack = sumBlack + ce.Value
    Next ce
End Function
Click to expand...
Omg! you nailed!! I don't know how long that took but it seemed like a really long time to find someone that could crack that one. Thank you very much for your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Formula to change font color in part of cell only
Replies
2
Views
339
Nathan Asius
N
U
  • Question
VBA: Change Font Color Based on Value
Replies
3
Views
1K
unknownymous
U
J
  • Question
Highlight in red all cells that have a formula with a mixed reference.
Replies
2
Views
279
Josecarlospdcj
J
D
  • Solved
VBA to sum red font and black font
Replies
7
Views
571
data808
D
D
  • Locked
  • Question
VBA for A15 cell to calculate red font values and C15 cell to calculate black font values
2
Replies
13
Views
570
Joe4
Joe4

Forum statistics

Threads
1,214,921
Messages
6,122,280
Members
449,075
Latest member
staticfluids

Share this page

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
Back
Top