FORMATGET

There are 10 functions.
These functions will not update automatically when the colour changes. You can press (Ctrl + Alt + F9) to refresh this function.


Remarks

* For instructions on how to add a function to a workbook refer to the page under Inserting Functions
* The equivalent JavaScript function is FORMATGET


Public Function FORMATGET( _ 
   ByVal rgeCell As Range, _
   ByVal sWhatToGet As String) As Variant

Dim sfontname As String
Dim lfontcolor As Long
Dim sfontsize As String
Dim scolorindex As String
Dim lcolor As Long
Dim sthemecolor As String
Dim oFormatCondition As FormatCondition
Dim sreturn As String

   Select Case UCase(sWhatToGet)
      Case "FONTNAME":
         sfontname = rgeCell.Font.Name
         sreturn = sfontname
      
      Case "FONTSIZE":
         sfontsize = rgeCell.Font.Size
         sreturn = sfontsize
      
      Case "FONTCOLOR":
         lfontcolor = rgeCell.Font.Color
         sreturn = ReturnRGB(lfontcolor)
         
      Case "FONTCOLORINDEX":
         scolorindex = rgeCell.Font.ColorIndex
         sreturn = scolorindex
         
      Case "FONTCOLORTHEME":
         sthemecolor = rgeCell.Font.ThemeColor
         sreturn = sthemecolor
         
      Case "COLOR":
         lcolor = rgeCell.Interior.Color
         sreturn = ReturnRGB(lcolor)
         
      Case "COLORINDEX":
         scolorindex = rgeCell.Interior.ColorIndex
         sreturn = scolorindex
         
      Case "COLORTHEME":
         sthemecolor = rgeCell.Interior.ThemeColor
         sreturn = sthemecolor

      Case "CONDITIONALCOLOR":
         If (rgeCell.FormatConditions.Count > 0) Then
             Set oFormatCondition = rgeCell.FormatConditions(1)
             lcolor = oFormatCondition.Interior.Color
             sreturn = ReturnRGB(lcolor)
         End If
         
      Case "CONDITIONALCOLORINDEX":
         If (rgeCell.FormatConditions.Count > 0) Then
             Set oFormatCondition = rgeCell.FormatConditions(1)
             scolorindex = oFormatCondition.Interior.ColorIndex
             sreturn = scolorindex
         End If
         
      Case "CONDITIONALFONTCOLOR":
         If (rgeCell.FormatConditions.Count > 0) Then
             Set oFormatCondition = rgeCell.FormatConditions(1)
             lfontcolor = oFormatCondition.Font.Color
             sreturn = ReturnRGB(lfontcolor)
         End If
         
      Case "CONDITIONALFONTCOLORINDEX":
         If (rgeCell.FormatConditions.Count > 0) Then
             Set oFormatCondition = rgeCell.FormatConditions(1)
             scolorindex = oFormatCondition.Font.ColorIndex
             sreturn = scolorindex
         End If
         
      Case Default:
   
   End Select

   FORMATGET = sreturn
End Function

Public Function ReturnRGB(ByVal lColorNumber As Long) As String Dim sreturn As String

   If (lColorNumber = 0) Then
      ReturnRGB = "RGB(0,0,0)"
      Exit Function
   End If

   sreturn = "RGB(" & (lColorNumber Mod 256)
   lColorNumber = (lColorNumber \ 256)
   sreturn = sreturn & "," & (lColorNumber Mod 256)
   lColorNumber = (lColorNumber \ 256)
   sreturn = sreturn & "," & (lColorNumber Mod 256) & ")"
   ReturnRGB = sreturn
End Function


FORMATGET_FONTNAME


Public Function FORMATGET_FONTNAME(ByVal rgeCell As Range) 
Dim sfontname As String
   sfontname = rgeCell.Font.Name
   FORMATGET_FONTNAME = sfontname
End Function

FORMATGET_FONTSIZE


Public Function FORMATGET_FONTSIZE(ByVal rgeCell As Range) 

Dim sfontsize As String

   sfontsize = rgeCell.Font.Size 
   FORMATGET_FONTSIZE = sfontsize
End Function

FORMATGET_FONTCOLOR


Public Function FORMATGET_FONTCOLOR(ByVal rgeCell As Range) 
Dim lfontcolor As Long
   lfontcolor = rgeCell.Font.Color
   FORMATGET_FONTCOLOR = ReturnRGB(lfontcolor)
End Function

FORMATGET_FONTCOLORINDEX


Public Function FORMATGET_FONTCOLORINDEX(ByVal rgeCell As Range) 
Dim scolorindex As String
   scolorindex = rgeCell.Font.ColorIndex
   FORMATGET_FONTCOLORINDEX = scolorindex
End Function

FORMATGET_FONTCOLORTHEME


Public Function FORMATGET_FONTCOLORTHEME(ByVal rgeCell As Range) 
Dim sthemecolor As String
   sthemecolor = rgeCell.Font.ThemeColor
   FORMATGET_FONTCOLORTHEME = sthemecolor
End Function

FORMATGET_COLOR


Public Function FORMATGET_COLOR(ByVal rgeCell As Range) 
Dim lcolor As Long
   lcolor = rgeCell.Interior.Color
   FORMATGET_COLOR = ReturnRGB(lcolor)
End Function


FORMATGET_COLORINDEX


Public Function FORMATGET_COLORINDEX(ByVal rgeCell As Range) 
Dim scolorindex As String
   scolorindex = rgeCell.Interior.ColorIndex
   FORMATGET_COLORINDEX = scolorindex
End Function


FORMATGET_COLORTHEME


Public Function FORMATGET_COLORTHEME(ByVal rgeCell As Range) 
Dim sthemecolor As String
   sthemecolor = rgeCell.Interior.ThemeColor
   FORMATGET_COLORTHEME = sthemecolor
End Function

FORMATGET_CONDITIONALCOLOR


Public Function FORMATGET_CONDITIONALCOLOR(ByVal rgeCell As Range) 
Dim oFormatCondition As FormatCondition
Dim lcolor As Long
   If (rgeCell.FormatConditions.Count > 0) Then
       Set oFormatCondition = rgeCell.FormatConditions(1)
       lcolor = oFormatCondition.Interior.Color
       FORMATGET_CONDITIONALCOLOR = ReturnRGB(lcolor)
   End If
End Function


FORMATGET_CONDITIONALCOLORINDEX


Public Function FORMATGET_CONDITIONALCOLORINDEX(ByVal rgeCell As Range) 
Dim oFormatCondition As FormatCondition
Dim scolorindex As String
   If (rgeCell.FormatConditions.Count > 0) Then
       Set oFormatCondition = rgeCell.FormatConditions(1)
       scolorindex = oFormatCondition.Interior.ColorIndex
       FORMATGET_CONDITIONALCOLORINDEX = scolorindex
   End If
End Function


FORMATGET_CONDITIONALFONTCOLOR


Public Function FORMATGET_CONDITIONALFONTCOLOR(ByVal rgeCell As Range) 
Dim oFormatCondition As FormatCondition
Dim lfontcolor As Long
   If (rgeCell.FormatConditions.Count > 0) Then
       Set oFormatCondition = rgeCell.FormatConditions(1)
       lfontcolor = oFormatCondition.Font.Color
       FORMATGET_CONDITIONALFONTCOLOR = ReturnRGB(lfontcolor)
   End If
End Function


FORMATGET_CONDITIONALFONTCOLORINDEX


Public Function FORMATGET_CONDITIONALFONTCOLORINDEX(ByVal rgeCell As Range) 
Dim oFormatCondition As FormatCondition
Dim scolorindex As String
   If (rgeCell.FormatConditions.Count > 0) Then
       Set oFormatCondition = rgeCell.FormatConditions(1)
       scolorindex = oFormatCondition.Font.ColorIndex
       FORMATGET_CONDITIONALFONTCOLORINDEX = scolorindex
   End If
End Function


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext