VBA Macro - Hardcode All Cells with a Specific RGB

[bkanne]

Hi there,

I'm hoping someone can help me write what (I think) should be a reasonably easily macro to run

The scenario is this - I've built a large, complicated roll-up model that kicks out individual cash flows on an asset-by-asset basis into a specified output. This model is too complicated / calculation intensive to run as the full operating model, so the cash flows need to be moved into a separate spreadsheet where I will build a separate valuation model

Cells within each worksheet are color coded accordingly: RGB (0-0-255) for inputs, and RGB (0-128-0) for cells that reference other worksheets

Instead of copying and pasting values & formats for a full sheet into the new workbook, I would instead like move the full worksheet, and then run a macro that hardcodes the cells that are already colored RGB (0-128-0) within the given active sheet

The reason for this - I want to keep the subtotal line items (which are in black) as calculating formulas, and it is tedious to copy and paste values row by row (there are many assets)

I've attached an example spreadsheet with 1) the raw version and 2) the desired output after running the macro

Thank you so much for any help!

Regards,
Ben

 

[Marc L]

Hi !​

​

I would instead like move the full worksheet

What that means ? What do you exactly need ? Elaborate with accuracy …​

​

 

[bkanne]

Fairly simple.

1) For the active worksheet, select all cells that have a font color of RBG 0-128-0
2) Copy and paste values for those cells
3) Color those cells RGB 0-0-255

That is all.

 

[Marc L]

As you just need to remove the links from cells then it's not such a great idea to work with colors so a VBA demonstration for starters :​

Sub Demo1()
    Sheets("RAW_EXAMPLE").Copy
    ActiveWorkbook.BreakLink "J:\20 Project GALAXY\02. Excel\01. Models\Cash Flow Rollup Model_v06.xlsm", 1
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[bkanne]

Thank you for the reply

In this case they may or may not be linked from another workbook - this is just an example

Understanding that it's ideal to work with colors...in this specific case, I need to. I have macros for breaking external links, etc - was just hoping someone could help me with this specific application

 

[Marc L]

So your bad 'cause of a not complete elaboration and an attachment with links …​

​

Anyway still avoiding any dumb color way :​

Sub Demo2()
             Dim Rg As Range
             Sheets("RAW_EXAMPLE").Copy
    With ActiveWorkbook.ActiveSheet
        For Each Rg In .Range("I10:I" & Split(.UsedRange.Address, "$")(4)).SpecialCells(xlCellTypeFormulas).Areas
                 Rg.Formula = Rg.Value2
            With Rg.Offset(, 6).Resize(, .UsedRange.Columns.Count - 14)
                .Formula = .Value2
            End With
        Next
    End With
End Sub

► Do you like it ? So thanks to click on bottom right Like ! ◄
​

 

[bkanne]

The attachment with links was intentional. That is how it will appear after the worksheet is moved from the first model to the second. I am sorry you didn't find my elaboration and example workbook sufficient. Thanks anyway.

 

[Marc L]

So with links as per your attachment the most efficient way is my first demonstration …​

​