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!

Register Yourself Login