Set Read Cell Attributes

[AlexanderBB]

Dim x As Range
Set x = Worksheets("Sheet1").Range("C2:C5")
Dim cell

For Each cell In x
  Debug.Print cell.Font.Color
  Debug.Print cell.Font.Name
  Debug.Print cell.Interior.Color
Next

Why does Debug.Print cell.Font.Color always return 0 regardless of the actual font color ?

This is giving an error, how might I correct it ?
Worksheets("Sheet1").Range("C2:C5").cell.font.name= "MS Sans Serif"

Thanks

 

[kweaver]

Change to:

Worksheets("Sheet1").Range("C2:C5").Font.Name = "MS Sans Serif"

 

[AlexanderBB]

Thanks for sorting that.
Anyone know the best plan for reading a cells Fore and Back color, then applying that to another worksheet.
It's proving to be a bit tricky as there's so many methods , all of which I've seen so far are just crazy complicated.
r-Click-FormatCells-Fill-MoreColors-Custom gives the right RGB value, but I can't get that into VBA (even recording a macro)

 

[kweaver]

Do you mean you want to do something like the following to apply the J3 font name to D12 and also apply the size?

Range("D12").Font.Name = Range("J3").Font.Name
Range("D12").Font.Size = Range("J3").Font.Size

 

[AlexanderBB]

That's sort of it, but they're on 2 diff computers and needs back & foreground colours. That's the bit I'm having trouble with.

 

[kweaver]

I don't know how you'd find the attributes of a workbook on one computer and then apply them to another computer's workbook.

You can find the background color (which is the interior color) or the font color using:

Range("D12").Interior.Color = Range("J3").Interior.Color
Range("D12").Font.Color = Range("J3").Font.Color