complicated macro in excel help?

Question

My boss would like a macro that highlights all formulas and cells use in a formula colour/color coated.
Kind of like what the when you go into formula mode with the (ctrl key and wave hyphon key) and press on a formula and all related cells are highlighted. Accept I want a macro to highlight every formula and related cells colour/color coated at the same time.
P.S this is probably a very hard macro to write if it is even possible but still thanks in advance to all.

Answer 1

Don't need to use a macro. Set the conditions and the colors you want and format the cells with Conditional Formatting. Lots easier.

Answer 2

When I first started to answer this I thought it was possible, but was going to be very hard to do. I thought you'd have to go through the formula character by character, but as I researched it a little I found that it is very easy to do.



There are two properties of the Range Object that help you out on this: the HasFormula property and the Precedents property or DirectPrecedents property.



The HasFormula property basically is easy enough to figure out. It tells you if the cell has a formula in it or not. The Precedents property tells you the cells that cell references directly or indirectly. The DirectPrecedents property tells you what cells that cell directly references.



So basically we create a little loop like this:



Dim MyRange As Range

Dim x As Range



Set MyRange = Range("G1:G10")



For Each x In MyRange

If x.HasFormula = True Then

x.Interior.ColorIndex = 3

x.DirectPrecedents.Interior.ColorIndex = 4

End If

Next x



The macro above basically searches through cells G1:G10 and every cell that has a formula it colors red. Every cell that is a direct precedent of that cell with a formula gets colored green. Although you may want to add only little tidbit more to it:



Dim MyRange As Range

Dim x As Range

Dim y As Range



Set MyRange = Range("G1:G10")



For Each x In MyRange

If x.HasFormula = True Then

x.Interior.ColorIndex = 3

For Each y In x.DirectPrecedents

If y.HasFormula = True Then

y.Interior.ColorIndex = 3

Else

y.Interior.ColorIndex = 4

End If

Next y

End If

Next x



This extra check makes sure that if the direct precedents have a formula that they are colored red, so that all of the cells with formulas are colored red. Or you might want to change the 3 on:



y.Interior.ColorIndex = 3



To



y.Interior.ColorIndex = 6



So that direct precedents with formulas are colored yellow.







I tried the code out and it worked. I tend to learn when I answer Excel macro questions on here, so I try to answer some on here every so often. Your problem was easier to address than I thought.



You can also use the Dependents property or DirectDependents property to find out if that cell affects other cells.

Answer 3

try something like this:



Selection.SpecialCells(xlCellTypeFormulas, 23).Interior.ColorIndex = 6



this code might be triggered by simply changing cells, or command button, or opening the workbook, just about any event you wanted.

Answer 4

Public Sub DoMyCopy() Dim sourcecol As Integer Dim targetcol As Integer ' reproduction first 2 cols Sheets("Sheet1").Columns("A:B").reproduction Sheets("Sheet2").selection("A1") sourcecol = 3 targetcol = 3 Sheets("Sheet1").go with on a similar time as Cells(2, sourcecol) <> "" If Cells(2, sourcecol) = "forecast" Then Sheets("Sheet1").Columns(sourcecol).reproduction Sheets("Sheet2").Columns(targetcol).previous... targetcol = targetcol + a million end If sourcecol = sourcecol + a million Wend ' clean chosen levels and reproduction alternatives application.CutCopyMode = fake Sheets("Sheet2").swap on Sheets("Sheet2").selection("a1").go with Sheets("Sheet1").swap on Sheets("Sheet1").selection("a1").go with end Sub