Copy cells to another sheet based on font properties?

[sepaltoona]

I have made a fairly elaborate finance spreadsheet on Sheet1 to track long term financial projections (tracking monthly bills over a year in the future). It contains currency values for known/expected bills and has many formulas. Paid bills in Sheet1 are in regular font, while future bills are in italics (so I know what has been paid vs. what hasn't). Sheet2 mirrors Sheet1, but only shows what bills are already paid up to now (manually updated to reflect current bank account values only after a bill has been paid and clears the bank). At present, when a bill has been paid I remove the italics from the value of a cell in Sheet1 (to mark it as paid), and then just copy it into the same cell on Sheet2. This is annoying to manually add the value from Sheet 1 (after removing the italics) to Sheet2 every time a bill gets paid.

I'm sure there is a way to automatically have the contents (a currency value) of a range of cells in Sheet1 added to the same range of cells in Sheet2, but only for the Sheet1 values that are italicized.

Hopefully someone here has an idea!

Thanks!

 

[sepaltoona]

Here's one way you could set up two sets of results in one sheet. It relies on:

- A custom Cell Styles format which I've called MyStyle (I've made mine bold, italic, greyed-out and different font)
- The user triggering the macro ToggleFormat using a shortcut key, e.g. CTRL-Shift-T, to toggle between MyStyle and normal format.
- The range names MonitoredRange (B1:D7 here) and TotalsExcluded (B10:D10).

When the macro is triggered, it adjust the formulae in TotalsExcluded, e.g. the current formula in B10 is =SUM($B$1,$B$2,$B$6)

The approach is not foolproof, as it relies on the user resisting the urge to change the cell format directly, rather than via CTRL-Shift-T.

Excel 2010

	A	B	C	D
1	Values	1	1	1
2		2	2	2
3		3	3	3
4		4	4	4
5		5	5	5
6		6	6	6
7		7	7	7
8
9	Sum	28	28	28
10	Exclude	9	4	3
11	Net sum	19	24	25

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Sub ToggleFormat()

    Dim rngMonitored As Range, rngTotalsExcluded As Range, rngCell As Range
    Dim s As String
    Dim r As Long, c As Long
    
    Set rngMonitored = Range("B1:D7")
    Set rngTotalsExcluded = Range("B10:D10")
    
    For Each rngCell In Selection
        With rngCell
            If .Style = "MyStyle" Then
                .Style = "Normal"
            Else
                .Style = "MyStyle"
            End If
        End With
    Next rngCell
    
    For c = 1 To rngMonitored.Columns.Count
        If Not Intersect(Selection, rngMonitored.Columns(c)) Is Nothing Then
            s = ""
            For r = 1 To rngMonitored.Rows.Count
                If rngMonitored(r, c).Style = "MyStyle" _
                    Then s = s & rngMonitored(r, c).Address & ","
            Next r
            If Len(s) Then
                s = "=Sum(" & Left(s, Len(s) - 1) & ")"
                rngTotalsExcluded(c).Formula = s
            Else
                rngTotalsExcluded(c) = 0
            End If
        End If
    Next c

End Sub

Interesting idea. This works, but only to an extent. A couple problems/annoyances still exist which would require other solutions:

1. Using custom cell styles means the colored backgrounds I want won't remain after toggling the macro in that cell -- is there a way to make a cell's background color not change when using a custom cell style?
2. Every time a new paycheck comes in, I will have to move the range in the "current balance" formula to include the next column over -- this is slightly annoying having to do this every 1-2 weeks.
3. Using a shortcut key to trigger a macro also isn't ideal (as you noted).

But maybe there is a better way to do what I originally wanted... to make a formula only include cell values that are bold. That way I can go out as far into the future with projections and the "current balance" total won't change until I tell it to include a value (by making a value have bold font when the bank account has updated to reflect that amount added/removed).

Essentially I'm trying to make an automatically updating bank account balance to be viewable within the spreadsheet.

Thanks -- keep the ideas coming!

 

[sepaltoona]

Bump

 

[Peter_SSs]

I'm going with a somewhat similar suggestion and I'm assuming you want to sum the bold cells. That is, not the un-italicised ones though the method could easily be change to that.

This method relies on the user changing cells from unBolded to Bolded, or vice-versa, individually by double-clicking the cell, not using the Ribbon or Ctrl+B keyboard shortcut. See further notes on this at the bottom of the post.

This code goes in the worksheet module for the sheet in question. that is, where the bold/unbold is performed.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True
  Target.Font.Bold = Not Target.Font.Bold
  ActiveSheet.Calculate
End Sub

This code goes in a standard module.

Function SpecSum(rRangeToSum As Range, Optional bFormatting As Boolean = True) As Single
  Dim rCell As Range
  
  Application.Volatile
  For Each rCell In rRangeToSum
    With rCell
      If .Font.Bold = bFormatting And IsNumeric(.Value) Then
        SpecSum = SpecSum + .Value
      End If
    End With
  Next rCell
End Function

Use the function in the worksheet as I have in cell G8 below to sum bold cells in a range (or ranges).
If you wanted to sum unbolded cells in a range (or ranges) use the function with the optional second argument set to False as I have done in cell G10.

Note that I have manually shaded the bold cells to ensure they were easily identifiable in this screen shot.

Excel Workbook
	F	G	H	I	J	K	L	M	N
1
2			1	2	3	4	5	6
3
4		10
5		10
6		10
7		10
8		23
9
10		10
11
Sheet2

Now try double-clicking individual cells in the formula target range(s) to bold/unbold them & note the formula results.


If cells are changed other than by double-clicking the formulas will not automatically update. You could force a calculation refresh with F9. Failing that a recalculation would be performed next time the workbook is opened, or we could force one every time a different cell/range is selected in the worksheet. However, if a cell/range is selected and formatted normally and the selection left as it is then no 'event' (that I know of) is triggered so there is nothing to 'capture' and force the re-calculation.
That is partly why, way back in post #4, I said that using a font property to do this task was not a great way to go.