VBA to change format of a linked cell

  • Thread starter Thread starter jenmoocat
  • Start date Start date
J

jenmoocat

I apologize if this is really simple, but I've been searching around
on the web and haven't found what I am looking for.... Maybe I am not
searching for the right thing.....

I would like to know the VBA syntax that I could use to change the
font format of a linked cell to be the same as the cell it is linked
to...

A simple example:
on sheet2, in cell b13, is the value 2.
It is colored blue and is bolded.

on sheet2, in cell b47, is the value 5.
it is formatted in the default manner

cell A1, on the sheet named sheet1, contains the formula ='sheet2!b13'
cell A2, on the sheet named sheet1, contains the formula ='sheet2!b47'

I want to run a macro that would result in the following:
the font in A1 to be colored blue and bolded, because that is the
formatting on sheet2,b13.
And I want the font in A2 to be the default, because that is the
formatting on sheet2,b47.

Can anyone give me some pointers?
I am not trained/skilled in VBA, and usually just find stuff on the
web that I adapt to my own ends.....

Help is much appreciated.

-jennifer.
 
This might get you started.

Sub a()
Dim Cell As Range
Dim SrcCell As Range
For Each Cell In Range("A1:A2")
With Cell
Set SrcCell = Range(Mid(.Formula, 2))
.Font.Bold = SrcCell.Font.Bold
.Font.ColorIndex = SrcCell.Font.ColorIndex
End With
Next
End Sub

This only works if the formulas are as you said, linked to a single cell.
It also depends on the sheet with the formulas being active.
 
Back
Top