Vba : Need help , extract contain 2 cell color/background

[muhammad susanto]

hi expert...

i have set data with contain 2 cell color, blue and yellow..
i want to extract which only data/value with contain blue, can be extract

product	after vba
mango (cell color yellow)
papaya (cell color blue)	papaya
orange (no cell color)
banana (cell color blue)	banana

<tbody>
</tbody>

it's possible??

for somebody help me, i appreciated....

thank in advance...........

 

[patel45]

Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
scolor = 5 ' to be changed
For r = 2 To LR
'  c = Cells(r, 1).Interior.ColorIndex ' to see color
  If Cells(r, 1).Interior.ColorIndex = scolor Then Cells(r, 2).Value = Cells(r, 1).Value
Next
End Sub

 

[muhammad susanto]

how to using? why is not working???

 

[patel45]

you have to know the number of blue color you are using and assign it to scolor, ther'is many blue.
To see color number uncomment the line

  c = Cells(r, 1).Interior.ColorIndex ' to see color

then run the code step by step with F8

if problems paste here a link to your file

 

[Kenneth Hobson]

This forum does not allow attachments so we have to guess what you did not explain.

A range's interior color or colorindex can be used. They are not the same value. The more complicated scenario is if you used conditional formats for these interiors.

Place the code in a module and run it with that sheet active.

Sub a()
  Dim LR As Long, ciRed As Integer, ciYellow As Integer, r As Long, c As Range
  ciRed = 3
  ciYellow = 6
  LR = Cells(Rows.Count, "A").End(xlUp).Row
  For r = 2 To LR
    Set c = Cells(r, 1)
    Debug.Print r, c.Address, c.Interior.ColorIndex ' to see colorindex in Immediate Window
    If c.Interior.ColorIndex = ciRed Or c.Interior.ColorIndex = ciYellow Then Cells(r, 2).Value = Cells(r, 1).Value
  Next
End Sub

 

[muhammad susanto]

thanks..Kenneth

your explanation is very well...
it's worked....