I have inserted the module to create a formula that will list a number associated the background color of an individual cell. I copied the VBA from the following website http://xldynamic.com/source/xld.ColourCounter.html#code. This formula works, however, it only works if either of the cells, in which the equation is referencing, is modified by clicking on the formula bar and then by pressing the enter key. Is there a way for the code to be modified or changing a setting in Excel to allow me to only change the background color of the cell without modifying the cell value for the equation output to be updated?

My ultimate goal is to link a cell from one worksheet to another worksheet and copy both the cell value and the cell background color. Is either of these options available?

Hi,

Am Mon, 21 Jan 2013 23:48:22 +0000 schrieb TreeHugger1:

Is there anyway to run a macro or change a setting to autosize
(autoadjust the height) of any merged cells in a spreadsheet. I have the
autowrap option checked on the merged cells. I am running Excel 2007.

untested:

Sub AutoFitMergedCellRowHeight()
'passt die Zeilenhöhe bei verbundenen Zellen automatisch an
'von Hans Herber
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
Dim iX As Integer
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + _
MergedCellRgWidth
iX = iX + 1
Next
MergedCellRgWidth = MergedCellRgWidth + (iX - 1) * 0.71
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

This post is for the wrong question (It answers another one of my posts though).

When I run the macro on a specific cell, it actually works. Thanks.

However, it only autosizes if the cell needs to be enlarged. If the cell is too large for the amount of verbiage in it, then the cell does not shrink to the appropriate size. Is this something that you can fix?

Also, the macro does not work on the entire sheet. It only works on the cell that you have highlighted when you run it. Is there a way for the macro to search the entire worksheet and autosize all of the merged cells without clicking on each one?

On Sun, 27 Jan 2013 23:28:44 +0000, TreeHugger1 wrote:


I want to show the formula for a specific cell in text format in another
cell.

For example, if cell A1 has a simple formula of "=10+64" (but only shows
an answer of "74"), how can i link this cell to B1, so that B1 only
shows as text "10+64" or " =10+64", instead of the actual answer?

You can do this with a User Defined Function (UDF).

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=showformula(A1)

in B1.


======================
Function ShowFormula(rg As Range) As String
ShowFormula = rg.Formula
End Function
=======================