Can we Get/Apply Information of the Conditional Formatting used in any cell using VBA?

Can we Get/Apply the Information of the Conditional Formatting used in any cell using VBA?

Dear Forum,

In one of my recent posts, I received help in getting the information of the Formula, Value and various aspects of Formatting..
However, is it possible to get the information for the Conditional Formatting which is applied in the cell whose information I seek..

Like for Example :

I need to find the information for a particular cell which has a conditional formatting such as if the value contains the number between 0-25 then the Font Colour is RED , if the value is 26-50 then Orange and if the Value is 51-75 then Green and if the last range 76-100 is Blue.

Now I know how to apply the same using conditional format dialog box manually however I need to extract this information from an already existing conditionally applied cell..as Information as well as I should be able to replicate this same information to a different Sheet using VBA..

Warm REgards
e4excel

Dear Forum,

One more thing I am also looking to get the information for the Borders used in the original Sheets data range to be replicated in the different sheet using this information...

Maybe something like this would work for border info.

Sub GetBorderInfo()
    Dim nBorder(7 To 12, 1 To 3) As Long, i As Long
    
    '
    ' Get Border Info
    '
    With Range("B3:E10")
        For i = 7 To 12
            With .Borders(i)
                nBorder(i, 1) = .LineStyle
                nBorder(i, 2) = .Weight
                nBorder(i, 3) = .Color
            End With
        Next i
    End With
    
    '
    ' Apply Border Info
    '
    With Range("G3:J10")
        For i = 7 To 12
            With .Borders(i)
                .LineStyle = nBorder(i, 1)
                .Weight = nBorder(i, 2)
                .Color = nBorder(i, 3)
            End With
        Next i
    End With
End Sub

Any Ideas on the below:

'XlLineStyle can be one of these XlLineStyle constants:
'xlContinuous
'xlDash
'xlDashDot
'xlDashDotDot
'xlDot
'xlDouble
'xlSlantDashDot
'xlLineStyleNone


'XlBorderWeight can be one of these XlBorderWeight constants:
'xlHairline
'xlThin
'xlMedium
'xlThick

'The color is specified as an index value into the current color palette, or as one of the following XlColorIndex constants:
'xlColorIndexAutomatic
'xlColorIndexNone

Thanks it worked like a charm, I was not able to use it for such a longtime as I made a mistake in your code.. by putting an "." before xlLineStyleNone
My mistake..

In most of my work I have used Dotted Lines as well as does that mean when I am considering these different aspects then i need to also consider the different sides too for each aspect..

Yeah, actually I was trying to do that but every time I am doing that my nets going bad..
Sorry...

Option Explicit

Sub GetFormatting1()

    Cells.Select
    Selection.Clear
    Selection.NumberFormat = "@"
    ActiveWindow.DisplayGridlines = False
    
    Dim nRow As Long, nCol As Long, nLastCol As Long, nLastRow As Long, i As Long
    Dim wsR As Worksheet, wsF As Worksheet, wsD As Worksheet
    Dim bGridlines As Boolean
    
    Set wsR = Worksheets("Report")
    Set wsF = Worksheets("Trial")
    Set wsD = Worksheets("Dump")
    
    nLastCol = 7    'wsR.Cells(1, Columns.Count).End(xlToLeft).Column
    nLastRow = 5   'wsR.Cells(Rows.Count, nLastCol).End(xlDown).Row

        wsR.Activate
        bGridlines = ActiveWindow.DisplayGridlines

        wsD.Activate
        wsD.Cells.Clear
    
    i = 0
    
    With wsR
        For nRow = 2 To nLastRow
            For nCol = 1 To nLastCol
                
                With .Cells(nRow, nCol)

                        wsF.Cells(i + 1, 1) = ""
                        wsF.Cells(i + 2, 1) = "/* Beginning of information for the cell " & .Address & " */" 'Chr(39)
                        wsF.Cells(i + 3, 1) = ".Address" & "  =  " & .Address
                        wsF.Cells(i + 4, 1) = ".Formula" & "  =  " & Chr(34) & WorksheetFunction.Substitute(.Formula, """", """""") & Chr(34)
                        wsF.Cells(i + 5, 1) = ".Value" & "  =  " & WorksheetFunction.Text(.Value, .NumberFormat)
                        wsF.Cells(i + 6, 1) = ".NumberFormat" & " =  " & Chr(34) & WorksheetFunction.Substitute(.NumberFormat, """", """""") & Chr(34)
                        wsF.Cells(i + 7, 1) = ".HorizontalAlignment" & "  =  " & .HorizontalAlignment
                        wsF.Cells(i + 8, 1) = ".VerticalAlignment" & "  =  " & .VerticalAlignment
                        wsF.Cells(i + 9, 1) = ".WrapText" & "  =  " & .WrapText
                        wsF.Cells(i + 10, 1) = ".Orientation" & "  =  " & .Orientation
                        wsF.Cells(i + 11, 1) = ".IndentLevel" & "  =  " & .IndentLevel
                        wsF.Cells(i + 12, 1) = ".Font.Name" & "  =  " & Chr(34) & .Font.Name & Chr(34)
                        wsF.Cells(i + 13, 1) = ".Font.Size" & "  =  " & .Font.Size
                        wsF.Cells(i + 14, 1) = ".Font.Bold" & "  =  " & .Font.Bold
                        wsF.Cells(i + 15, 1) = ".Font.Italic" & "  =  " & .Font.Italic
                        wsF.Cells(i + 16, 1) = ".Font.Underline= .Font.Underline"
                        wsF.Cells(i + 17, 1) = ".Font.Strikethrough" & "  =  " & .Font.Strikethrough
                        wsF.Cells(i + 18, 1) = ".Font.Superscript" & "  =  " & .Font.Superscript
                        wsF.Cells(i + 19, 1) = ".Font.Subscript" & "  =  " & .Font.Subscript
                        wsF.Cells(i + 20, 1) = ".Font.Color" & "  =  " & .Font.Color
                        wsF.Cells(i + 21, 1) = ".Font.ColorIndex" & "  =  " & .Font.ColorIndex
                        wsF.Cells(i + 22, 1) = ".Interior.Color" & "  =  " & .Interior.Color
                        wsF.Cells(i + 23, 1) = ".Interior.ThemeColor" & "  =  " & .Interior.ThemeColor
                        wsF.Cells(i + 24, 1) = ".Font.TintAndShade" & "  =  " & .Interior.TintAndShade
                        wsF.Cells(i + 25, 1) = ".ColumnWidth" & "  =  " & .ColumnWidth
                        wsF.Cells(i + 26, 1) = ".RowHeight" & "  =  " & .RowHeight
                        wsF.Cells(i + 27, 1) = ".Borders.ColorIndex " & "  =  " & .Borders.ColorIndex
                        wsF.Cells(i + 28, 1) = ".Interior.PatternColor" & "  =  " & .Interior.PatternColorIndex
                        wsF.Cells(i + 29, 1) = ".Interior.PatternThemeColor" & "  =  " & .Interior.PatternThemeColor
                        wsF.Cells(i + 30, 1) = ".Interior.TintAndShade" & "  =  " & .Interior.TintAndShade
                        wsF.Cells(i + 31, 1) = ".Interior.PatternTintAndShade" & "  =  " & .Interior.PatternTintAndShade
                        
       
                        wsF.Cells(i + 32, 1) = "/* End of the information for the cell " & .Address & " */" 'Chr(39)
                        wsF.Cells(i + 33, 1) = ""
                        wsF.Cells(i + 34, 1) = "'*---*---*---*---*---*---*---*---*---*---*---*---*---*---*---*---*---*"
                        
                        
                       

'Windows("Report").DisplayGridlines = Windows("Dump").DisplayGridlines
'Windows(2).DisplayGridlines = Windows(1).DisplayGridlines

        wsD.Activate
        ActiveWindow.DisplayGridlines = bGridlines
        ActiveWindow.FreezePanes = False


        
wsD.Cells(nRow, nCol).Formula = .Formula
wsD.Cells(nRow, nCol).Value = .Value
wsD.Cells(nRow, nCol).NumberFormat = .NumberFormat
wsD.Cells(nRow, nCol).HorizontalAlignment = .HorizontalAlignment
wsD.Cells(nRow, nCol).VerticalAlignment = .VerticalAlignment
wsD.Cells(nRow, nCol).WrapText = .WrapText
wsD.Cells(nRow, nCol).Orientation = .Orientation
wsD.Cells(nRow, nCol).IndentLevel = .IndentLevel
wsD.Cells(nRow, nCol).Font.Name = .Font.Name
wsD.Cells(nRow, nCol).Font.Size = .Font.Size
wsD.Cells(nRow, nCol).Font.Bold = .Font.Bold
wsD.Cells(nRow, nCol).Font.Italic = .Font.Italic
wsD.Cells(nRow, nCol).Font.Underline = .Font.Underline
wsD.Cells(nRow, nCol).Font.Strikethrough = .Font.Strikethrough
wsD.Cells(nRow, nCol).Font.Superscript = .Font.Superscript
wsD.Cells(nRow, nCol).Font.Subscript = .Font.Subscript
'wsD.Cells(nRow, nCol).Font.Color = .Font.Color .Font.ColorIndex    ' .Interior.Color      '
'MsgBox ConditionalColor(wsR.Cells(nRow, nCol), "Font")
wsD.Cells(nRow, nCol).Font.ColorIndex = ConditionalColor(wsR.Cells(nRow, nCol), "Font")

wsD.Cells(nRow, nCol).Interior.ColorIndex = ConditionalColor(wsR.Cells(nRow, nCol), "Interior")
'wsD.Cells(nRow, nCol).Interior.ThemeColor = .Interior.ThemeColor
'wsD.Cells(nRow, nCol).Font.TintAndShade = .Font.TintAndShade
wsD.Cells(nRow, nCol).ColumnWidth = .ColumnWidth
wsD.Cells(nRow, nCol).RowHeight = .RowHeight



' For the EdgeTop Border
If wsR.Cells(nRow, nCol).Borders(xlEdgeTop).LineStyle <> xlLineStyleNone Then
    wsD.Cells(nRow, nCol).Borders(xlEdgeTop).LineStyle = wsR.Cells(nRow, nCol).Borders(xlEdgeTop).LineStyle
    wsD.Cells(nRow, nCol).Borders(xlEdgeTop).Weight = wsR.Cells(nRow, nCol).Borders(xlEdgeTop).Weight
    wsD.Cells(nRow, nCol).Borders(xlEdgeTop).ColorIndex = wsR.Cells(nRow, nCol).Borders(xlEdgeTop).ColorIndex
Else
    wsD.Cells(nRow, nCol).Borders(xlEdgeTop).LineStyle = xlLineStyleNone
End If
        
                End With
                i = i + 34
                
            Next nCol

            
        Next nRow
        
        wsD.Range("H2").Select
        ActiveWindow.FreezePanes = True
        wsD.Range("A1").Select
        Application.ScreenUpdating = False
        
    End With
    
    
    wsF.Activate
    wsF.UsedRange.Font.Size = 9
    wsF.UsedRange.Font.Bold = False
    wsF.UsedRange.Rows.HorizontalAlignment = xlLeft
    wsF.UsedRange.Rows.VerticalAlignment = xlCenter
    wsF.UsedRange.Rows.RowHeight = 12
    wsF.UsedRange.ColumnWidth = 42
    wsF.UsedRange.IndentLevel = 1
   ' wsF.Range("A:A").ColumnWidth = 84
    wsF.UsedRange.Columns.WrapText = True
    wsF.UsedRange.Rows.AutoFit
    wsF.UsedRange.Borders.Color = 1
    
    'Application.CutCopyMode = False
    Application.ScreenUpdating = False
    
    
    wsF.Rows("$1:$1").Select
    ActiveWindow.FreezePanes = True
    wsF.Rows("$1:$1").RowHeight = 33
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    
End Sub

If a cell has a Font Color red due to a conditional formatting applied on the cells value can we record just the value for the RED Color for the Font..

Yes, that is easy. But I would have assumed that you wanted the conditional formatting applied rather than just the color. And I'm unsure how to do that.

Please correct the code wherever neccessary

Let me mention that you have a nice style of writing the VBA code which even a formula person like me feels easy to understand.

Thank you. That is, of course, intentional. And I worked hard to develop that style. Although, as a Chess Coach I'm used to taking complex ideas and expressing them so that those with less experience can understand them.

Ok Understood now that the numbers 7 to 12 stand for the various sides of a cell, but just out of curiosity, why it did not start from 1, then what does 1 to 6 or 5 stand for?

How do they say this: "It is outside my pay grade." :-)

What I did was this. I recorded a macro where I applied borders to a region. Then I looked at the recorded code and played around with it. As I played around with it, I made the discoveries which I illustrated in my previous message. But to be honest, that is the limit of my knowledge. I don't know any more than what I showed you.

it gave me the Diagonal Lines though it was not present in the original..

I stated as such in my previous post. So I think my procedure has limitations and isn't foolproof.

Hi StevenM,

Thanks for the response, I tried the code it does make the Borders but somehow strangely the colours are getting interchangedTHe color for the Inside Verticals and Inside Horizontals are getting interchanged..

Can you please help me with , getting the colour for the conditional formatting used..
Is it different than what you ave helped in the earlier query

Yes, that is easy. But I would have assumed that you wanted the conditional formatting applied rather than just the color. And I'm unsure how to do that.

If the latter is not possible then can I just get the color information for a cell which has derived the color FillCOLOR and FONT color FONT size any such changes which can be possible due to conditional formatting..

Thanks in advance..

Hi Joseph,

Actually both, but if the latter is extremely difficult then just getting the information would be fine, but then let me just confirm one thing..
In certain cells we have a default Fill Color, Font Color , Font Size and on the conditional formatting condition meeting the appearance changes so will it give me the information of the existing color or the color which is got changed due to the conditional formatting.

Thanks in advance

Hi Steven.

If thats possible please let me know, so that I can close the thread.
The problem is that when I replicated the Report sheet which has default FOnt and Fill Color as NO COLOR it just pastes the contents with the same default color ignoring the COnditional Formatting which is rendering different FOnt and Fill colors based on the contents in the cell.

Thanks in advance

Okay, I think I've solved the problem of formatting conditions.

Take a look at: e4excel_CreateTable_v2.xlsm

And run: Run_CreateTable

I realize that you have said you don't like this approach, but perhaps you can adapt from my code what you need.

There is one mistake:

'.Font.Color = 16777215 ' Don't add until interior.colors are added

The first apostrophe in the above line needs to be deleted. It turns the font color white, and before I added the conditional formatting, a white font on a white background looks blank.

Yeah but those codes are so superior that I find it too difficult to extend or add to it and therefore I liked your approach simple yet can be improvised.
Actually I making some Sheets where I need to get the replicas but when I tried just copy pasting it worked but it takes too much time and I think this approach is better to re-create a sheet using everything as if formulas and the decided formatting and then just convert it into values.

It saves on time and the coding does not get revealed..

Dear StevenM,

First of all, your conditional formatting doesn't have a font color. I believe I had mentioned this before.

I think I have made changes in the file and as my PC and net is slow and am not able to trace the old file, however I am uploading the File in contention to save trouble..

I have made a few changes in the File..
There are 4 Sheets:
1 Data
2 Report
3 Formatting Information - This is used to store the information of the Range
4 Replica - This is to create the replica of the Report Sheet.

In the Replica, everything gets written i.e the contents the Regular Formats like Font Color which is done Manually in the Report sheet except for the column D where there's conditional formatting used..
The contents do get copied but do not get displayed, strangely they have a White Font Color and the same regarding the Fill Color..there's no change in that though there are 5 different Conditional Formats set in the Report Sheet.

I liked the last solution, I mean its so good that I think one doesn't need to even specify the range, however how do I use that in my case..

Please advise on the same..
Thanks in advance..

Dear Forum,

I found a link for my problem where I need the color codes for cells filled with conditional formatting.
There's a link but I have some issues with my Download mgr and unable to do so..
Can someone please try downloading and pasting it here as I can download from the forum without any hassles.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=190

Thanks

Dear Steven,

I found this code in the earlier link, now how I use this code over your code whenever there's a conditional formatting applied, I mean that I dont want to specify which cells has conditional formatting or not whichever has it they need to use this code and dont then your code and the color code should be derived..

Function ConditionalColor(rg As Range, FormatType As String) As Long
     'Returns the color index (either font or interior) of the first cell in range rg. If no _
    conditional format conditions apply, Then returns the regular color of the cell. _
    FormatType Is either "Font" Or "Interior"
    Dim cel As Range
    Dim tmp As Variant
    Dim boo As Boolean
    Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
    Dim i As Long
     
     'Application.Volatile    'This statement required if Conditional Formatting for rg is determined by the _
    value of other cells
     
    Set cel = rg.Cells(1, 1)
    Select Case Left(LCase(FormatType), 1)
    Case "f" 'Font color
        ConditionalColor = cel.Font.ColorIndex
    Case Else 'Interior or highlight color
        ConditionalColor = cel.Interior.ColorIndex
    End Select
     
    If cel.FormatConditions.Count > 0 Then
         'On Error Resume Next
        With cel.FormatConditions
            For i = 1 To .Count 'Loop through the three possible format conditions for each cell
                frmla = .Item(i).Formula1
                If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True
                     'Conditional Formatting is interpreted relative to the active cell. _
                    This cause the wrong results If the formula isn 't restated relative to the cell containing the _
                    Conditional Formatting--hence the workaround using ConvertFormula twice In a row. _
                    If the Function were Not called using a worksheet formula, you could just activate the cell instead.
                    frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
                    frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel)
                    boo = Application.Evaluate(frmlaA1)
                Else 'If "Value Is", then identify the type of comparison operator and build comparison formula
                    Select Case .Item(i).Operator
                    Case xlEqual ' = x
                        frmla = cel & "=" & .Item(i).Formula1
                    Case xlNotEqual ' <> x
                        frmla = cel & "<>" & .Item(i).Formula1
                    Case xlBetween 'x <= cel <= y
                        frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")"
                    Case xlNotBetween 'x > cel or cel > y
                        frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")"
                    Case xlLess ' < x
                        frmla = cel & "<" & .Item(i).Formula1
                    Case xlLessEqual ' <= x
                        frmla = cel & "<=" & .Item(i).Formula1
                    Case xlGreater ' > x
                        frmla = cel & ">" & .Item(i).Formula1
                    Case xlGreaterEqual ' >= x
                        frmla = cel & ">=" & .Item(i).Formula1
                    End Select
                    boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
                End If
                 
                If boo Then 'If this Format Condition is satisfied
                    On Error Resume Next
                    Select Case Left(LCase(FormatType), 1)
                    Case "f" 'Font color
                        tmp = .Item(i).Font.ColorIndex
                    Case Else 'Interior or highlight color
                        tmp = .Item(i).Interior.ColorIndex
                    End Select
                    If Err = 0 Then ConditionalColor = tmp
                    Err.Clear
                    On Error GoTo 0
                    Exit For 'Since Format Condition is satisfied, exit the inner loop
                End If
            Next i
        End With
    End If
     
End Function

Dear Steven,

May I please request you to add the two codes and then upload an attachment..
I am not able to follow what and where I need to make the additions with your code as well as the code which I mentioned in the Post # 29 and the link for the same in the Post # 28..

Are these the lines where I need to make the additions :

'wsD.Cells(nRow, nCol).Font.Color = .Font.Color .Font.ColorIndex    ' .Interior.Color      '

wsD.Cells(nRow, nCol).Font.ColorIndex = .Font.ColorIndex
wsD.Cells(nRow, nCol).Interior.Color = .Interior.Color
'wsD.Cells(nRow, nCol).Interior.ThemeColor = .Interior.ThemeColor
'wsD.Cells(nRow, nCol).Font.TintAndShade = .Font.TintAndShade

So humble request upload the Sheet where you have made the changes as well as see the other code if thats more superior in terms of handling more criteria not that your code isn't it..
I honestly liked your code as it mentions the condition criteria and then the FOnt Color and Interior Fill COlor but as far as this requirement goes I would need just the Color Code(s) for Font as well as Fill Color..

Function ConditionalColor(rg As Range, FormatType As String) As Long
     'Returns the color index (either font or interior) of the first cell in range rg. If no _
    conditional format conditions apply, Then returns the regular color of the cell. _
    FormatType Is either "Font" Or "Interior"
    Dim cel As Range
    Dim tmp As Variant
    Dim boo As Boolean
    Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
    Dim i As Long
     
     'Application.Volatile    'This statement required if Conditional Formatting for rg is determined by the _
    value of other cells
     
    Set cel = rg.Cells(1, 1)
    Select Case Left(LCase(FormatType), 1)
    Case "f" 'Font color
        ConditionalColor = cel.Font.ColorIndex
    Case Else 'Interior or highlight color
        ConditionalColor = cel.Interior.ColorIndex
    End Select
     
    If cel.FormatConditions.Count > 0 Then
         'On Error Resume Next
        With cel.FormatConditions
            For i = 1 To .Count 'Loop through the three possible format conditions for each cell
                frmla = .Item(i).Formula1
                If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True
                     'Conditional Formatting is interpreted relative to the active cell. _
                    This cause the wrong results If the formula isn 't restated relative to the cell containing the _
                    Conditional Formatting--hence the workaround using ConvertFormula twice In a row. _
                    If the Function were Not called using a worksheet formula, you could just activate the cell instead.
                    frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
                    frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel)
                    boo = Application.Evaluate(frmlaA1)
                Else 'If "Value Is", then identify the type of comparison operator and build comparison formula
                    Select Case .Item(i).Operator
                    Case xlEqual ' = x
                        frmla = cel & "=" & .Item(i).Formula1
                    Case xlNotEqual ' <> x
                        frmla = cel & "<>" & .Item(i).Formula1
                    Case xlBetween 'x <= cel <= y
                        frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")"
                    Case xlNotBetween 'x > cel or cel > y
                        frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")"
                    Case xlLess ' < x
                        frmla = cel & "<" & .Item(i).Formula1
                    Case xlLessEqual ' <= x
                        frmla = cel & "<=" & .Item(i).Formula1
                    Case xlGreater ' > x
                        frmla = cel & ">" & .Item(i).Formula1
                    Case xlGreaterEqual ' >= x
                        frmla = cel & ">=" & .Item(i).Formula1
                    End Select
                    boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
                End If
                 
                If boo Then 'If this Format Condition is satisfied
                    On Error Resume Next
                    Select Case Left(LCase(FormatType), 1)
                    Case "f" 'Font color
                        tmp = .Item(i).Font.ColorIndex
                    Case Else 'Interior or highlight color
                        tmp = .Item(i).Interior.ColorIndex
                    End Select
                    If Err = 0 Then ConditionalColor = tmp
                    Err.Clear
                    On Error GoTo 0
                    Exit For 'Since Format Condition is satisfied, exit the inner loop
                End If
            Next i
        End With
    End If
     
End Function

So please thanks in advance..

Warm Regards
e4excel

The only thing I do not follow is when I tried to get the information for the cells with no BORDERS i.e cells selected with FORMAT - BORDERS - NONE but they still produce a regular border.
I want the code to record some value which means that there is no BORDER

So that was the flaw in my code, but the following corrects that.

Sub GetBorderInfo()
    Dim nBorder(7 To 12, 1 To 3) As Long, i As Long
    
    '
    ' Get Border Info
    '
    With Range("B3:E10")
        For i = 7 To 12
            With .Borders(i)
                nBorder(i, 1) = .LineStyle
                nBorder(i, 2) = .Weight
                nBorder(i, 3) = .Color
            End With
        Next i
    End With
    
    '
    ' Apply Border Info
    '
    With Range("G3:J10")
        For i = 7 To 12
            With .Borders(i)
                If Not nBorder(i, 1) = xlLineStyleNone Then
                    .LineStyle = nBorder(i, 1)
                    .Weight = nBorder(i, 2)
                    .Color = nBorder(i, 3)
                End If
            End With
        Next i
    End With
End Sub

My assumption is that if you add .weight or .color to a border, it will add a default line style. But if you check if the line style is = xlLineStyleNone before, you can stop the code from adding a border.

Thanks Steven very sweet of you to reply so quickly actually I was searching for the condition to check whether it .xlLineStyleNone when you replied..
One more thing , I am using your code but in the old style from the first help..

Can you please help me on the same, a I have yet to use the above new approach, I mean i m going to use it later on but I started with the old approach..

'---For Line Style
                        wsF.Cells(i + 32, 1) = ".Borders(xlEdgeTop).LineStyle" & "  =  " & .Borders(xlEdgeTop).LineStyle

This was to display..
and this is to actually format the new sheets border based on the REPORT Sheets Border style.

This line gives me the FOrmatting but how do i check whether it is NONE or not?

wsD.Cells(nRow, nCol).Borders(xlEdgeTop).LineStyle = wsR.Cells(nRow, nCol).Borders(xlEdgeTop).LineStyle

How do I write the NOT condition in the above line..

And also, while researching I found out that there are several other aspects about Line Borders which are also important such as the below:

 'XlLineStyle can be one of these XlLineStyle constants:
'xlContinuous
'xlDash
'xlDashDot
'xlDashDotDot
'xlDot
'xlDouble
'xlSlantDashDot
'xlLineStyleNone


'XlBorderWeight can be one of these XlBorderWeight constants:
'xlHairline
'xlThin
'xlMedium
'xlThick

'The color is specified as an index value into the current color palette, or as one of the following XlColorIndex constants:
'xlColorIndexAutomatic
'xlColorIndexNone

Thanks in advance..

its not working at all..

An If statement is a true or false statement. A not negates a Boolean statement or variable.

So maybe something like:

If Not wsR.Cells(nRow, nCol).Borders(xlEdgeTop).LineStyle = xlLineStyleNone Then
    wsD.Cells(nRow, nCol).Borders(xlEdgeTop).LineStyle = wsR.Cells(nRow, nCol).Borders(xlEdgeTop).LineStyle
End If

Or

If wsR.Cells(nRow, nCol).Borders(xlEdgeTop).LineStyle <> xlLineStyleNone Then
    wsD.Cells(nRow, nCol).Borders(xlEdgeTop).LineStyle = wsR.Cells(nRow, nCol).Borders(xlEdgeTop).LineStyle
End If

Or something like that.

Try this.
It collects all the information for borders for each cell.
Then it recreates those borders.

In the line:

RecreateBorders Range("B3:E10"), Range("G3:J10")

The first range already has a border.
The second range will copy the border info of the first range.

Sub RecreateBorders(rgFrom As Range, rgTo As Range)
    Dim nBorder() As Long, i As Long
    Dim nRow As Long, nCol As Long
        
    ReDim nBorder(1 To rgFrom.Rows.Count, 1 To rgFrom.Columns.Count, 6 To 12, 1 To 3)
    For nRow = 1 To rgFrom.Rows.Count
        For nCol = 1 To rgFrom.Columns.Count
            For i = 6 To 12
                With rgFrom.Cells(nRow, nCol).Borders(i)
                    nBorder(nRow, nCol, i, 1) = .LineStyle
                    nBorder(nRow, nCol, i, 2) = .Weight
                    nBorder(nRow, nCol, i, 3) = .Color
                End With
            Next i
        Next nCol
    Next nRow
    For nRow = 1 To rgFrom.Rows.Count
        For nCol = 1 To rgFrom.Columns.Count
            For i = 6 To 12
                With rgTo.Cells(nRow, nCol).Borders(i)
                    If Not nBorder(nRow, nCol, i, 1) = xlLineStyleNone Then
                        .LineStyle = nBorder(nRow, nCol, i, 1)
                        .Weight = nBorder(nRow, nCol, i, 2)
                        .Color = nBorder(nRow, nCol, i, 3)
                    End If
                End With
            Next i
        Next nCol
    Next nRow
End Sub

Sub Run_RecreateBorders()
    RecreateBorders Range("B3:E10"), Range("G3:J10")
End Sub

Theres no error which is being thrown but inspite of using the Function ConditionalColor its not working and the Column D in the Dump SHeet remains without any color..

SO I want you to please correct me where I am mistake in entering the code for FOnt and Interior in the lines where the coloring happens for the Dump Sheet

Conditional Formatting information for the applied color and not the Formula..
I am just reiterating that I would be happy if I could just get the Color or COlorIndex , Fill COlor, Font Size:- No(s) or any other aspect which conditional formatting can influence i.e superficial color no derived from conditional formatting..

So if a cell has a default black font color and the conditional formatting renders it a REd color then can I get the answer as the COLOR or COLORINDEX NO for RED color and not BLACK..

I'm not for sure what you're asking.

As for Color & Colorindex, I suspect that Color will always be more precise and that one shouldn't use colorindex. But I could be wrong.

I don't have a lot of experience working with color formatting. I've worked on two workbooks with it, yours and one other. In that other workbook, I just copied the color formatting, and pasted it into another cell. So really, everything I know has been by playing around with your workbook.

In the file you uploaded, the color formatting was a phrase plus an interior color. The way I understood it, the color of the font was just the property of the cell, but perhaps I could be wrong about that. Hmm ... I just ran a test, and I was not able to pick up the font color from the formatting. So unless I made a mistake, I was right. The font color was separate from the conditional formatting.

No doubt conditional formatting can be more complex than what is in your workbook. But in your workbook, the color formatting was 4 sets of a phrases and interior color for each phrase. It would be easy to retrieve that information and store it as needed.

I did a Google and found: http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx
I have no idea if it will help you or not.

PLease check post 29 , 28 and I have pasted the code in modules in my file

I don't think I know anything on this issue more than what I've already posted. Frankly, it has been a learning experience for me, and I thank you for that. I've learned a lot playing around with your spreadsheet and exploring these issues. But I've never understood exactly what you were doing, or why you wanted to do it the way you are doing it. If I felt that I could help you again in some small way, you know I would. My non-response is merely due to the fact that I've exhausted all my knowledge on this issue and I don't think I have anything to add. As for the code you posted, how would I know if it worked or not? I can't just read code and know what it will do. As my bio makes clear, I don't claim to have anything more than a rudimentary understanding. While I'm willing to share my rudimentary understanding when I can, time and time again I've seen others post code far superior to mine.

Are you using the same workbook as before? (If so I have a copy of it.) If not, can you upload your workbook?

Yeah the same one from the other query where you helped

So can you post the whole code, and point out which part is giving you problems?

Now I actually could understand some part of it, really appreciate your patience with me..
How the code was working..

But I am still lost ,as though your function is extremely useful I am not sure how to go about further?
I was even trying to integrate the values from the other function but in vain..

I think all the major part is over except the tail..

What I am asking is? In the uploaded File , if you click the command button in the Formatting Information Sheet, it goes to the Replica Sheet and starts creating the exact replica however in the column D though the values appear they do not get displayed as the Font is White or No Color..
I only want this to have the same color as the Parent Sheet REport Colund, In the Parent sheet the colors are rendered by applying conditional formatting whereas over here I want it to be applied using VBA,

Using these two lines I was applying color nos

wsD.Cells(nRow, nCol).Font.Color =  .Font.Color

wsD.Cells(nRow, nCol).Interior.Color = .Interior.Color

However the normal functions used were not recording the color nos acquired due to Conditional Formatting, now with your coding as well as the other code I can get the colors but do not how to pass it to these above lines..

As this is happening in a loop and each cell has a different format is that a challenge?

Where did you get the function ConditionalColor? It wasn't part of the code you posted.

Theres no error which is being thrown but inspite of using the Function ConditionalColor its not working and the Column D in the Dump SHeet remains without any color..

SO I want you to please correct me where I am mistake in entering the code for FOnt and Interior in the lines where the coloring happens for the Dump Sheet

First of all, your conditional formatting doesn't have a font color. I believe I had mentioned this before.

Run the following code on your worksheet.

Function StripFromQuotes(s As String) As String
    Dim sArray() As String
    sArray = Split(s, Chr(34))
    StripFromQuotes = sArray(1)
End Function

Function GetFormatConditions(rg As Range) As String
    Dim i As Long, s As String
    With rg.Cells(1, 1)
        If .FormatConditions.Count > 0 Then
            For i = 1 To .FormatConditions.Count
                With .FormatConditions(i)
                    s = s & StripFromQuotes(.Formula1) & ","
                    s = s & .Interior.Color & ","
                    s = s & .Font.Color & ","
                End With
                s = s & vbCr
            Next i
        End If
    End With
    If Len(s) Then GetFormatConditions = Left$(s, Len(s) - 1)
End Function

Sub Run_GetFormatConditions()
    Dim sArray1() As String, sArray2() As String, i As Long, s As String
    s = GetFormatConditions(Worksheets("Report").Range("D2:D11"))
    MsgBox s
    sArray1 = Split(s, vbCr)
    For i = LBound(sArray1) To UBound(sArray1)
        sArray2 = Split(sArray1(i), ",")
        MsgBox "Formula1 = " & sArray2(0)
        MsgBox "InteriorColor = " & sArray2(1)
        MsgBox "FontColor = " & sArray2(2)
    Next i
End Sub

I mean its so good that I think one doesn't need to even specify the range, however how do I use that in my case..

You must supply the function GetFormatConditions with at least one cell within the range which has format conditions. Then from that one cell, it can pick up all five conditions.

The function returns a string with three items for each condition: (a) Formula1, interior color and font color. So with format conditions more complex than that, my code will be inadequate.

Also note, Formula1 cannot contain a comma, because I use a comma as a marker in the string. (Of course, if a comma is used in Formula1, my marker can be changed.)

The string is returned with two types of markers: (1) vbCr is used to separate the different format conditions, and (2) a comma is used to separate each element in a condition.

Dim sArray1() As String, sArray2() As String, i As Long

'...

sArray1 = Split(GetFormatConditions(Worksheets("Report").Range("D2:D11")), vbCr)
' Now each item in sArray1 from LBound(sArray1) To UBound(sArray1) contains one condition!
sArray2 = Split(sArray1(i), ",")
' Now each item in sArray2 equals one of three elements within a condition. So that:
Formula1 = sArray2(0)
Interior Color = sArray2(1)
and Font Color = sArray2(2)

Of course, you need to replace "Formula1, Interior Color, and Font Color" with where ever you want to write these values.

What part of my last message did you not understand?

Hi Steve,

Honestly, I thought that the last post #43 contained the code from the actual excerpts from the function but now after re-reading your post several times I realized that I missed that as a part of the Function..

I am not so familiar with VBA and the last 2-3 codes and functions are a little too heavy for me..I mean i have not understood them completely but still get the results well but when it comes to applying it does reveal my folly..

Uptill now I was just asking , if it was possible to get the formula of the conditional formatting but I think you have already made provisions for the same..

I dont have anything as Formula1 till now, I mean I have the cell formula in my code which you had provided which gives the formula in the cell but now that is already happening the things remaining were the Font Color and Fill Color or the Interior Color incase of the Conditional Formatting Applied Cells..

wsD.Cells(nRow, nCol).Font.Color =  Font Color -- Replaced with variables from post # 43 but it did not work

wsD.Cells(nRow, nCol).Interior.Color = Interior Color -- Replaced with variables from post # 43 but it did not work

Just to confirm that I need to add those lines in the Code in the Sheet Formatting instructions Right?

Option Explicit

Sub GetFormatting1()
Dim rng As Range

Dim sArray1() As String, sArray2() As String, i As Long

'...

sArray1 = Split(GetFormatConditions(Worksheets("Report").Range("D2:D11")), vbCr)
' Now each item in sArray1 from LBound(sArray1) To UBound(sArray1) contains one condition!
sArray2 = Split(sArray1(i), ",")
' Now each item in sArray2 equals one of three elements within a condition. So that:
        Formula1 = sArray2(0)
        Interior Color = sArray2(1)
        Font Color = sArray2(2)

Like this nah..

Warm regards
e4excel

Originally Posted by StevenM

What part of my last message did you not understand?

Dear Steve,

Sincere apologies, but believe me
Though I like all your solutions/codes provided till now it still is difficult for me to really adapt so fast..
So please please forgive me for that..

I still don't know what you are asking.

The function I wrote: GetFormatConditions collects thee items from format conditions: (a) Formula1, (b) interior color, and (c) font color.

I would assume that format conditions can get more complex than that, so if you need more information than that, my GetFormatConditions function won't be of much use. But it seems to me that this might be acceptable for your use.

One of the problems is that a cell can contain more than one format condition. And my function returns them all.

But it returns them all in one long string. I don't know what your needs are, nor what you are trying to accomplish.

In your last workbook, you had five format conditions, and with three items per each, that is 15 pieces of information (all in one lone string).

As I tried to illustrate in message #43, there is a simple way of getting to this 15 pieces of data by using the Split function. The first split divides the string in 5 parts (based on 5 formatting conditions). The second split divides each of the 5 parts into three parts: (a) Formula1, (b) interior color, and (c) font color.

So if you want to write their information into a cell, one cell for each item, then you have to loop.
Maybe something like:

Dim sArray1() As String, sArray2() As String, i As Long
Dim nRow As Long, nCol As Long

'...

sArray1 = Split(GetFormatConditions(Worksheets("Report").Range("D2:D11")), vbCr)
' Now each item in sArray1 from LBound(sArray1) To UBound(sArray1) contains one condition (with three items each)!
For i = LBound(sArray1) To UBound(sArray1)
    sArray2 = Split(sArray1(i), ",")
    ' Now each item in sArray2 equals one of three items within a condition. So that:
    Cells(nRow, nCol) = "Formula1 = " & sArray2(0)
    Cells(nRow + 1, nCol) = "Interior Color = " & sArray2(1)
    Cells(nRow + 2, nCol) = "Font Color = " & sArray2(2)
    nRow = nRow + 3
Next i

You need to modify Cells, nRow, nCol to suit your needs.

Are you wanting to collect the data of the formatting conditions as text?
Or are you trying to set the formatting conditions in a range?

Message #48 assumes the latter.
If you are trying to set format conditions, you would need something like:

Dim sArray1() As String, sArray2() As String, i As Long
Dim nRow As Long, nCol As Long
Dim oFC As FormatCondition

'...

With .Range("D2:D" & whateverRow)
    sArray1 = Split(GetFormatConditions(Worksheets("Report").Range("D2:D11")), vbCr)
    ' Now each item in sArray1 from LBound(sArray1) To UBound(sArray1) contains one condition (with three items each)!
    For i = LBound(sArray1) To UBound(sArray1)
        sArray2 = Split(sArray1(i), ",")
        ' Now each item in sArray2 equals one of three items within a condition. So that:
        Set oFC = .FormatConditions.Add(xlCellValue, xlEqual, sArray2(0))
        oFC.Interior.Color = Val(sArray2(1))
        oFC.Font.Color = Val(sArray2(2))
    Next i
End With

You would need to modify .Range & whateverRow.