Remove Color from cells without using Select

  • Below code does nothing, need solution

    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?

    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:


    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:

    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)

    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!