Retrieve cell value & format at the same time

[LuisE]

I’m looping thru a range using two variables that will transfer the outcome
to another sheet. I want to retrieve the cell value and the font format so it
carries any bolds. (No paste special...)

Variables-loop 1

NameLEG = Sheet1.Cells(RowLEG, 2) & " " & Sheet1.Cells(RowLEG, 3)
StreetLEG = Sheet1.Cells(RowLEG, 4)



Outcome-loop2
Worksheets("Labels").Cells(LastRowLabel, LastColLabel).Value = NameLEG
Worksheets("Labels").Cells(LastRowLabel + 1, LastColLabel).Value = StreetLEG

Thanks in advance

 

[jasontferrell]

The "Font" class is read-only for the Range, so something like this
doesn't work:
Set ar = ActiveCell.Font
Set rng = ActiveCell.Offset(3, 0)
rng.Value = ActiveCell.Value
Set rng.Font = ar
There are about 10-15 properties of the Font class that you could set
individually, something like this:
Set ar = ActiveCell.Font
Set rng = ActiveCell.Offset(3, 0)
rng.Value = ActiveCell.Value
rng.Font.Background = ar.Background
rng.Font.Color = ar.Color
rng.Font.ColorIndex = ar.ColorIndex
rng.Font.FontStyle = ar.FontStyle
rng.Font.Italic = ar.Italic
(and so on, or maybe there are only a few values that you care about,
such as only using Bold)
Or you could use the copy and pastespecial(all) methods like this:
Set rng = ActiveCell.Offset(3, 0)
ActiveCell.Copy
rng.PasteSpecial xlPasteAll
Application.CutCopyMode = False
The downside here would be that you couldn't store the values to be
copied all at once, then paste, you'd have to copy-paste, copy-paste,
copy-paste (which may be what you're doing anyway).

 

[Homey]

NameLEG = Sheet1.Cells(RowLEG, 2) & " " & Sheet1.Cells(RowLEG, 3)
NameLEGBold = Sheet1.Cells(RowLEG, 2).Font.Bold Or Sheet1.Cells(RowLEG,
3).Font.Bold
StreetLEG = Sheet1.Cells(RowLEG, 4)
StreetLEGBold = Sheet1.Cells(RowLEG, 4).Font.Bold

--

Worksheets("Labels").Cells(LastRowLabel, LastColLabel).Value = NameLEG
Worksheets("Labels").Cells(LastRowLabel, LastColLabel).Font.Bold =
NameLEGBold
Worksheets("Labels").Cells(LastRowLabel + 1, LastColLabel).Value = StreetLEG
Worksheets("Labels").Cells(LastRowLabel + 1, LastColLabel).Font.Bold =
StreetLEGBold

| I’m looping thru a range using two variables that will transfer the
outcome
| to another sheet. I want to retrieve the cell value and the font format so
it
| carries any bolds. (No paste special...)
|
| Variables-loop 1
|
| NameLEG = Sheet1.Cells(RowLEG, 2) & " " & Sheet1.Cells(RowLEG, 3)
| StreetLEG = Sheet1.Cells(RowLEG, 4)
|
|
|
| Outcome-loop2
| Worksheets("Labels").Cells(LastRowLabel, LastColLabel).Value = NameLEG
| Worksheets("Labels").Cells(LastRowLabel + 1, LastColLabel).Value =
StreetLEG
|
| Thanks in advance
|