VBA include cell formatting not just .value

[Luke777]

Hi all,

I've got some code running that does what I want just fine. But I was wondering if it was possible to change the .value part of the code to something that includes not only the cell value, but also the way the cell is formatted (fill, font, align etc. you get the idea).

Only asking because if it is possible, it saves running an additional bit of code with the sole purpose of making things pretty - you know how it often is with these things, sometimes presentation is as, if not more, important than the actual data.

I understand if its the kind of thing that depends on what the code is doing too, so here's mine

Sub FillData3()

   Dim Cl As Range
   Dim dic As Object
   
   Set dic = CreateObject("scripting.dictionary")
   
   With Sheets("Sheet1") 'the sheet with values and formatting
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         dic(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
   End With
   
   With Sheets("Sheet2") 'where I would like formatting to appear
      For Each Cl In .Range("C4:C148", Range("C4").End(xlToRight))
         If dic.Exists(Cl.Value) Then Cl.Value = dic(Cl.Value)
      Next Cl
   End With
   
End Sub

Thanks all!

 

[offthelip]

The code you have got is extremely efficient in doing what it does, it will be very difficult to store the formatting values in the dictionary unless the formatting you want to store is very simple. This is because each item of formatting would need to be saved separately in the dictionary. e.g font size, font color , row height, italics, etc, Then there is a problem that if you changed the formatting of the source to add a new bit of formatting you would have to change the code in the dictionary. The way you are currently doing it by running some code to format the output is almost undoubtedly the fastest and easiest way of doing this.

 

[Luke777]

The code you have got is extremely efficient in doing what it does, it will be very difficult to store the formatting values in the dictionary unless the formatting you want to store is very simple. This is because each item of formatting would need to be saved separately in the dictionary. e.g font size, font color , row height, italics, etc, Then there is a problem that if you changed the formatting of the source to add a new bit of formatting you would have to change the code in the dictionary. The way you are currently doing it by running some code to format the output is almost undoubtedly the fastest and easiest way of doing this.

Thanks for the response

It's a shame there's not a straight up replacement for .value that could juggle it all

but thanks for your response and explanation!