Changing an existing formatting macro

[xluser2007]

Hi All,

At work, we often have check formulae in cells e.g. A1 =10, and A2 =10, then a check formula in A3 to esnure that A1=A2, would read as:

A3 = if(A1-A3=0, "a", "r")

The answer will in this example be "a". If you format this in Webdings format, the "a" becomes a tick and the "r" a cross - hence a useful eye check.

I've written a simple macro as follows to format using the Webdings format:

Sub tickformat()
ActiveCell.Select
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.ColorIndex = 3
.Font.Name = "Webdings"
End With
End Sub

This works, but only if I select ONE cell. How do I generalise this so that I can select any number of cells and then change format essentially recreating the BOLD or ITALICS funtion but in a differenyt format.

Any help would be appreciated.

(Note as per yesterdays discussion, this has not been cross posted, though if I ever accidentally do again, I know that Simon Llyod will keep me honest :))

[mikerickson]

Omit the first line.

'ActiveCell.Select:Rem Remove Me

[xluser2007]

mikerickson,

Thanks, that worked!

I am just tring to modify it a bit more, where the stated formula is applied to the cell and also where by a "a" will get coloured green (index=50) and a "r" or otherwise will get coloured red (index =3).

I have modified it as below:

Sub tickformat()

Selection.FormulaR1C1 = "=IF(ROUND(R[-1]C-R[-2]C,0)=0,""a"",""r"")"

If Selection.Value = "a" Then

With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.ColorIndex = 3
.Font.Name = "Webdings"
End With

Else

With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.ColorIndex = 50
.Font.Name = "Webdings"
End With

End If

End Sub

But finds a 'Debug Mismatch error' in line [Code]If Selection.Value = "a" Then[\Code]

Any suggestions on how to fix this, and to make the code more elegant?

regards

[mikerickson]

To compare values you need to check each cell in the Selection



Sub tickformat()
Dim oneCell as Range
Selection.FormulaR1C1 = "=IF(ROUND(R[-1]C-R[-2]C,0)=0,""a"",""r"")"

For Each oneCell in Selection
oneCell.font.ColorIndex = IIF(CStr(oneCell.Value) = "a",50,3)
next oneCell

End Sub

Rather than a macro that you have to call, you might consider using Conditional Formatting to change the "a" cells format.

[xluser2007]

mikerickson,

That is very nice, extremely clean and elegant.

The finished version for my purpose looks like this:

Sub Tickformat()

Dim oneCell as Range

Selection.FormulaR1C1 = "=IF(ROUND(R[-1]C-R[-2]C,0)=0,""a"",""r"")"

For Each oneCell in Selection

With oneCell

.Font.ColorIndex = IIF(CStr(oneCell.Value) = "a",50,3)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Name = "Webdings"

End With

next oneCell

End Sub
Last question, I want to assign this to a custom macro button in a toolbar.

How do I do this permanently? I was trying to save it as an addin i.e. Tickformat.xla then try to link the button to this addin/ macro.

This didn't work, what is the best way to link a custom command button permanently to this without creating Personal.xls?

[mikerickson]

An Add-In would be the best way to do that. But I am not well versed in that area of Excel.

Except for the conditional formatting, you could do everything else by defining a Style.