display cells with fill colour

[martinsimart]

Hi

I have a spreadsheet that colours cells yellow if they have a duplicate name listed in next column

as the column is long (500 rows)I was hoping that there was vba code to copy the small number of cells with fill colour and automatically paste them in another column so i can see them quickly

I know i could filter by colour but I am intrigued (lazy) to see if it can be done

Thanks

 

[Mentor82]

Hi,
Why don't you consider vba colour filtering instead of copying data? Check this out, it works for column A and color yellow. If you want to change either column or color adjust below range for columns and for color you can use the following statemeng RGB(255,255,0) [set your rgb color] instead of constant color values as vbyellow.

Sub ColorFiltering()
Dim rng as range

set rng =range"A1:A500"
ActiveSheet.rng.autofilter field:=1, Criteria1:=vbYellow, operator:=xlFilterCellColor

Set rng =nothing
End sub