Set font of cells based on contents of others

[lilgto64]

Here is what I am trying to do.

Have a reference column with numbers for example 1 through 10 in Column A - with a corresponding symbol in Column B - then in a working area - be able to type a given letter in one cell and have the appropriate symbol appear in the next cell. The catch is that the symbols in Column B may be of different type faces.

What I have done so far is to group the symbols such that 1-8 are a given type face and 9 and above are a second type face - but this requires that the font be applied after the working area is filled in. I would like it to be entirely dynamic so that if I decide to change the typeface in the reference area - the working area is automatically updated.

I would have liked to do this with formulas and conditional formatting - but if VBA is the only way that is okay.

So generically:
Set A1 to a static value in plain type
Set B1 to a static value in a symbol typeface

User input (or random number generator) in D1
Lookup value in D1 from the A:B static set and set E1 to the symbol value
Apply whatever the typeface of B1 happens to be to cell E1

I know there are other ways such as index etc - but vlookup works really well for me in many cases.

hope that makes sense.

<table border=2 cellpadding="5" cellspacing="5">
<tr><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr>
<tr><td>1</td><td></td><td></td><td>2</td><td</td></tr>
<tr><td>2</td><td</td><td></td><td>3</td><td>xx</td></tr>
<tr><td>3</td><td>xx</td><td></td><td>1</td><td></td></tr>
</table>

 

[crimson_b1ade]

right-click your sheet tab and select 'view code', then paste the below:

forgot to mention: in column E in the sheet you can use the lookup method of your choice. the below code simply matches the row and copies the adjacent cells format and applies it.

Private Sub Worksheet_Calculate()
 
Application.ScreenUpdating = False
 
On Error Resume Next
 
Dim SearchColumn As Integer
Dim MatchColumnLastRow As Long
Dim MatchColumn As Integer
Dim i As Long
Dim r As Long
 
MatchColumnLastRow = Range("D" & Rows.Count).End(xlUp).Row
SearchColumn = 1
MatchColumn = 4
 
For i = 1 To MatchColumnLastRow
    r = WorksheetFunction.Match(Cells(i, MatchColumn), Columns(SearchColumn), 0)
    Cells(r, SearchColumn + 1).Copy
    Cells(i, MatchColumn + 1).PasteSpecial Paste:=xlPasteFormats
Next i
 
With Application
    .CutCopyMode = False
    .ScreenUpdating = True
End With
 
End Sub

 

[lilgto64]

Thanks - had some issues trying to use a random number in one of the fields - I suspect because the random generator and the VBA code were looping into each other (or something along those lines).

Editing the spreadsheet with the macro enabled crashed Excel quite a bit - note that I am using Office for Mac 2011 - 14.1.0 (110310).

I modified the code like so:

Private Sub Worksheet_Calculate()

Application.ScreenUpdating = False

On Error Resume Next

Dim SearchColumn As Integer
Dim MatchColumnLastRow As Long
Dim MatchColumn As Integer
Dim i As Long
Dim r As Long

MatchColumnLastRow = Range("D" & Rows.Count).End(xlUp).Row
SearchColumn = 1
MatchColumn = 5
MatchColumn2 = 8

For i = 1 To MatchColumnLastRow
r = WorksheetFunction.Match(Cells(i, MatchColumn), Columns(SearchColumn), 0)
Cells(r, SearchColumn + 2).Copy
Cells(i, MatchColumn + 1).PasteSpecial Paste:=xlPasteFormats
Next i

For i = 1 To MatchColumnLastRow
r = WorksheetFunction.Match(Cells(i, MatchColumn2), Columns(SearchColumn), 0)
Cells(r, SearchColumn + 2).Copy
Cells(i, MatchColumn2 + 1).PasteSpecial Paste:=xlPasteFormats
Next i

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

End Sub

Since I actually have a couple columns that I want to have updated and some other columns etc used in the doc - but simplified it a bit in my first post. I imagine I could combine the two loops by adding an r2 variable - would simplify the code a bit and might even be a little faster that way as well.

It seems that changing the font of a cell is not quite enough to trigger the macro - if I change a symbol I may also have to edit the contents of a cell to trigger it. might be easier (safer?) to trigger the macro with a button or keyboard shortcut - so I can make all my changes - then click Apply to run through the loops to lookup and apply the formatting.


There does not seem to be a way to attach a file here.

 

[crimson_b1ade]

you're right, the code I provided won't be triggered by formatting changes.

using a button to trigger the macro manually sounds best. rather than trying to trigger based on some event (which is what I was trying to do).

if you want to 'upload' a file you will have to use one of the various free file sharing websites and post the link here.

unfortunately, i don't own a mac so I'm not sure how much help i'd be in addressing mac specific issues.