Announcement

Collapse
No announcement yet.

Remove colours in a sheet

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Remove colours in a sheet



    I have data which is highlighted in green colour in sheet2
    Now i have to remove only the colours from the data in sheet2

  • #2
    You will need to work out what colour "green" you have on sheet 2 (you can use the macro recorder to do this, record when you fill a cell with the green colour).

    One way to do this is to find all the green coloured cells on the sheet and then remove the fill colour from those cells.

    The following should work if the cells actually have a fill colour, it won't work if the colour is created by eg, conditional formatting.


    Code:
    Sub ClearGreenCells()
      Dim wsSht As Worksheet
      Dim rRange As Range
    
      'set a variable equal to sheet2
      Set wsSht = ThisWorkbook.Sheets(2)
      
      'set the Find format to "green"
      'need to set this to whatever "green" colour is being used
      With Application.FindFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
      End With
      
      'find all the green cells on the sheet
      Set rRange = FindAllInRange("", wsSht.UsedRange)
        
      'clear the Find format
      Application.FindFormat.Clear
      
      'finally clear the green coloured cells that were found
      If Not rRange Is Nothing Then
        With rRange.Interior
          .Pattern = xlNone
          .TintAndShade = 0
          .PatternTintAndShade = 0
        End With
      End If
    End Sub
    
    
    'this function will find all the cells that match the "LookFor" tag
    'in the range defined by LookRng and return them as a range
    Private Function FindAllInRange(ByVal LookFor As Variant, _
                                    ByRef LookRng As Range, _
                                    Optional ByVal fSearchWhole As Boolean = True, _
                                    Optional ByVal fByCols As Boolean = False, _
                                    Optional ByVal fMatchCase As Boolean = False) As Range
        
      Dim rRange As Range, sFirst As String
      
      With LookRng
        Set rRange = .Find(What:=LookFor, After:=.Cells(1, 1), _
                           LookIn:=xlFormulas, LookAt:=IIf(fSearchWhole, xlWhole, xlPart), _
                           SearchOrder:=IIf(fByCols, xlByColumns, xlByRows), SearchDirection:=xlNext, _
                           MatchCase:=IIf(fMatchCase, True, False), SearchFormat:=True)
                          
        'there is a bug in FindNext when using SearchFormat, so need to use Find instead
        'https://support.microsoft.com/en-us/help/282151/macro-that-uses-findnext-to-search-for-specific-format-finds-wrong-cel
        If Not rRange Is Nothing Then
          Set FindAllInRange = rRange
          sFirst = rRange.Address
          Do
            Set FindAllInRange = Union(FindAllInRange, rRange)
            'find the next cell starting from after where the last cell was found
            Set rRange = .Find(What:=LookFor, After:=rRange, _
                               LookIn:=xlFormulas, LookAt:=IIf(fSearchWhole, xlWhole, xlPart), _
                               SearchOrder:=IIf(fByCols, xlByColumns, xlByRows), SearchDirection:=xlNext, _
                               MatchCase:=IIf(fMatchCase, True, False), SearchFormat:=True)
          Loop While Not rRange Is Nothing And rRange.Address <> sFirst
        End If
      End With
      
      Set rRange = Nothing
    End Function 'FindAllInRange

    Comment


    • #3


      Thnx bro i love this forum and members too

      Comment

      Working...
      X