• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Color Values

Shabbo

Member
Dear Sir,
I have sheet one and two I have colored in sheet one now I generated one more file and wanted to color as same as sheet1 but I do it single single it will take too long can you please suggest me any formula.
 

Attachments

  • Color.xlsx
    8.3 KB · Views: 9
There is no such formula. In the main, color is irrelevant to Excel calculation (though one can filter on color I believe).
You would need VBA (a macro) to identify text matches and reproduce the cell interior color.
 
How about
Code:
Sub Shabbo()
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   Dim Cl As Range
  
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("sheet2")
   Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         If Cl.Value <> "" Then .Item(Cl.Value) = Cl.Interior.Color
      Next Cl
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then Cl.Interior.Color = .Item(Cl.Value)
      Next Cl
   End With
End Sub
 
Back
Top