Details of formatted cell

[HaworthEstates]

Hello there

I need some help with a problem:
I have a table, imported by Copy/Paste function, like the one below:

The values in column C and D are manually inserted, for the purpose of the problem.
How can I get these values (Column C and D) in an automation mode (functions or VBA)?

Many thanks in advance
Haworth

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub HaworthEstates()
   Dim Cl As Range
   
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Cl.Offset(, 2).Value = Cl.Font.FontStyle
      Cl.Offset(, 3).Value = GetHexFromLong(Cl.Font.Color)
   Next Cl
End Sub
Function GetHexFromLong(lngColour As Long) As String
   Dim R As String, G As String, B As String

   R = Format(Application.Dec2Hex(lngColour Mod 256), "00")
   G = Format(Application.Dec2Hex((lngColour \ 256) Mod 256), "00")
   B = Format(Application.Dec2Hex(lngColour \ 65536), "00")
   
   GetHexFromLong = "#" & R & G & B
End Function

 

[HaworthEstates]

Hi & welcome to MrExcel.
How about

Sub HaworthEstates()
   Dim Cl As Range
  
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Cl.Offset(, 2).Value = Cl.Font.FontStyle
      Cl.Offset(, 3).Value = GetHexFromLong(Cl.Font.Color)
   Next Cl
End Sub
Function GetHexFromLong(lngColour As Long) As String
   Dim R As String, G As String, B As String

   R = Format(Application.Dec2Hex(lngColour Mod 256), "00")
   G = Format(Application.Dec2Hex((lngColour \ 256) Mod 256), "00")
   B = Format(Application.Dec2Hex(lngColour \ 65536), "00")
  
   GetHexFromLong = "#" & R & G & B
End Function

Thank you so much. The Problem is the function report font as "Regular" for all cells!
Can you help me with this one?

Thanks a lot.

 

[Fluff]

Is the font style & colour set manually, or by conditional formatting?

 

[HaworthEstates]

Is the font style & colour set manually, or by conditional formatting?

Manually.

 

[Fluff]

Does it give the right colour?
As it works for me

+Fluff v2.xlsm
	A	B	C	D
1	Name
2	John Doe 1		Bold	#FF0000
3	John Doe 2		Italic	#000000
4	John Doe 3		Bold Italic	#B1A0C7
5	John Doe 4		Regular	#000000
6	John Doe 5		Regular	#000000
7	John Doe 6		Italic	#FF0000
8	John Doe 7		Bold Italic	#00B0F0
9	John Doe 8		Regular	#000000
10	John Doe 9		Regular	#FFC000
11	John Doe 10		Bold	#DA9694
Data

 

[HaworthEstates]

Is the font style & colour set manually, or by conditional formatting?

Sorry for the previous reply. The data is copied from a report on the web. While pasting into the excel worksheet, it shows the fonts as Bold or Regular, but on direct inspection all of them are Regular.
The color extraction works perfectly.

 

[Fluff]

Not sure if this will make a difference

Sub HaworthEstates()
   Dim Cl As Range
   
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Cl.Offset(, 2).Value = Cl.DisplayFormat.Font.FontStyle
      Cl.Offset(, 3).Value = GetHexFromLong(Cl.Font.Color)
   Next Cl
End Sub

 

[HaworthEstates]

Not sure if this will make a difference

Sub HaworthEstates()
   Dim Cl As Range
  
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Cl.Offset(, 2).Value = Cl.DisplayFormat.Font.FontStyle
      Cl.Offset(, 3).Value = GetHexFromLong(Cl.Font.Color)
   Next Cl
End Sub

Not working, but it is fine. I came across another way to calculate, regardless of the Font properties. Still, I will use the color function, which helps me a lot.
THANK YOU.

 

[Fluff]

You're welcome & thanks for the feedback.