VBA - Cut and paste based on cell colour

[Taff]

Hi Guys,

looking for some help with some VBA code, which involves cutting and pasting a row dependent on a cell colour.

there are 2 actions required, which will lead me on to a few others that I will require to write, but any help with these would point me in the right direction to complete those.

Actions required:

1.Check column AQ, if any cell is the colour green, then copy the whole row and paste in a worksheet called Check. There will be several entries per session, I also need the rows pasted in the worksheet to run in a timely order, ie not copied over the last entry?

2. Check column AI and if the value in a cell is between 1.02 and 2.20, then copy whole row and paste into a worksheet called XGOA.


as always any assistance greatly appreciated.


Cheers

Taff

 

[MARK858]

Are any columns blank/empty?

 

[shg]

the worksheet has 43 columns in it, and we are asking it to look at column 35 ...

I can't see your worksheet.

 

[Taff]

Ok I got it sorted.

Yes there were blanks within the filtered area, once filled in - all worked fine.

Couldn't pick up the coloured cells, but the colour was generated by conditional formatting, based on a value, so I changed the filter criteria to pick up the cell value.

Thank you all, as always, for the help on this.

Cheers

Taff

 

[shg]

Filtering based on CF cell color works fine, but the color needs to match.

Filtering based on the cell value makes more sense, though.

 

[vcoolio]

Hello Taff,

Wow! Alot happened here on this thread whilst I was watching football finals last night (and then sleeping it off!).

Looks like you're all sorted now. Mark858's question in post #11 ratifies why it is always a good idea to supply a sample of a workbook in the opening post. We can see what we're up against that way.

Someone discovered that 3 works the same as xlUp, and used that instead, just to prove they could be clever in obscuring their code. That's the Range.End(3)

I'm not sure exactly what shg implies with this comment, especially the part in bold italics, but I can assure you that there isn't a secret society of code writers who are obsessed with code secrecy.

In my case, its a simple case of not being a very good typist and I'd rather use an enumeration for the sake of brevity than type out all the associated text. Even with enumeration I'd rather type 3 than -4162 (xlUp). I'll use enumeration whenever I can remember the correct one to use! I could also be classed as lazy (not secretive)!

Its also good to see that you have made the effort to sort the minor problems out for yourself.

Anyway, good luck with it all and thanks for the feedback.

Cheerio,
vcoolio.

 

[MARK858]

Ok I got it sorted.

Yes there were blanks within the filtered area, once filled in - all worked fine.

If that is the case then probably the area of the currentregion wasn't what you thought it was.
Just for info the definition of the currentregion is

The current region is a range bounded by any combination of blank rows and blank columns.

so basically it is a range of contiguous data around the referenced cell. I also tend to agree with shg with his statement that it makes more sense to filter on the values (and I think that applies to most things in Excel if you have a choice).

Anyway happy you have it "sorted".