Remove Color from cells without using Select

  • Below code does nothing, need solution


    Code
    Sub NoYellow()
    
    
    If ActiveSheet.UsedRange.Interior.Color = Excel.XlRgbColor.rgbYellow Then ActiveSheet.UsedRange.Interior.Color = xlNone
    
    
    End Sub
  • How does you code integrate into your project, spread sheet? Could a For...Next loop be used?


    Code
    For i = 1 to 100
      [INDENT]For j = 1 to 20[/INDENT]
      [INDENT=2]If ActiveSheet.Cells(i,j).Interior.Color = Excel.XlRgbColor.rgbYellow Then ActiveSheet.Cells(i,j).Interior.Color = xlNone[/INDENT]
      [INDENT]Next j[/INDENT]
      Next i



    Though I must admit I haven't seen too much of this used:

    Code
    Excel.XlRgbColor.rgbYellow


    If the color is not exactly Excel.XlRgbColor.rgbYellow it might just return a false in the IF statement.



    I would generally use a more standard approach:


    Code
    Cells(i,j).Interior.Color =vbYellow
    
    
    ' or 
    
    
    Cells(i,j).Interior.Color = RGB(255, 255, 0)


    [INDENT=2] [/INDENT]

  • In your first code: is i a row and j a column?
    That code works, but is there a way to modify it to automatically detect what the used range is, rather than having to ball park it in the code?

  • How about looping through the UsedRange? (I can't see how you can test the whole of the UsedRange at once - it could contain many different colours)


    Code
    Sub NoYellow()
        Dim rngCell As Range
    
    
        For Each rngCell In ActiveSheet.UsedRange
            If rngCell.Interior.Color = Excel.XlRgbColor.rgbYellow Then rngCell.Interior.Color = xlNone
        Next rngCell
        
    End Sub
  • I tested three different colors present simultaneously, covering both - area with data in cells and outside of data area. All rgbYellow converted to xlNone - just as desired.


    Thank you both!


    Total range in test was about A1:X40, hopefully will work just as well in greater ranges...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!