Hi All,
I'm very new to VBA and also Excel Forum! I am trying to create a macro which will search through a range of cells and identify those which are a specific colour (conditionally formatted). And then copy some of the cells in the same row to another worksheet.
I am able to write the code to copy based on other features (i.e. cell names) but can't seem to work out how to copy based on specific colours. Is there a way to do this?
Best
Hi Whitethorn,
You cannot read the cell color of cells formatted by conditional formatting because the cell property for color is not changed by conditional formatting. (CF)
But no worries. the color of the row was a result of a specific formula to have the CF show up. so you can use the same criteria used in the CF to find those rows and copy them
Hi Whitehorn,
Welcome to the forum
In Excel 2010 you CAN do this... does not work in 2007, and not sure about 2013 or above.
A cell's .Interior.Color property will not show color from conditional formatting, but .DisplayFormat.Interior.Color WILL
Please help by:
Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know
There are 10 kinds of people in this world... those who understand binary, and those who don't.
Thank you so much this is really helpful! I've managed to almost get to where I want to be - I hadn't come across the DisplayFormat.Interior.Color syntax anywhere!
I just have one final question - so far I have been looking only in one column to find the conditional formatting but now I would like to look across a range of cells (so multiple columns) and i can't seem to figure out the code to do so.
I'm sure it's very simple but apparently not for newbies!
Please help!
Thank you
without more detail I don't know exactly what you need, but a loop like this example would look at each cell in a range:
Please Login or Register to view this content.
Hopefully the following will help. Essentially I have a range of cells that I would like to look through for the specific colour
I currently have the following code and would like to look through cells H11:AQ1090 on the input sheet to find the colour.
Please Login or Register to view this content.
Last edited by Whitethorn; 05-08-2018 at 08:42 AM.
Right now you find the last row in your code, but your post also says you want to check H11 to AQ1090... is AQ1090 the last cell this time, or will you ALWAYS want it to go down to that row?
Last edited by Arkadi; 05-08-2018 at 09:10 AM. Reason: Removed moderation request.
Looking at the code, you copy columns B through G if a highlight is found... so just to be clear on the requirement:
You want to look at H11 to AQ1090 and if any cell in a row is highlighted then you want to copy B:G of that row to the output sheet?
Yes you've got it - copy columns A to G
AQ1090 is currently the last row but it might not always be.
Thanks for your help!
Last edited by Whitethorn; 05-08-2018 at 08:52 AM.
Sorry whitehorn, 2 more questions...
1. Silly question, I removed this one.
2. On the output sheet, you want data pasted in Columns I to N? Starting at the first blank row? Does output have headers? I noticed you are clearing output I:G but that's only 3 columns, yet you are pasting 6 columns?
Last edited by Arkadi; 05-08-2018 at 08:56 AM.
It might not always be the last row (sorry i only saw this part after i posted before) and column B can always be relied upon to have data.
Yes on the columns I to N starting with the first blank row. Yes the output does have headings - So everything being cleared is below the headers (the I:G is my error, it should be clearing all 6 columns)
Thank you
Let's see if this works for you:
PS thanks for fixing your earlier postPlease Login or Register to view this content.
Last edited by Arkadi; 05-08-2018 at 10:58 AM. Reason: fixed typo
Thanks so much Arkadi.
It worked perfectly the first time that I ran it, however the second time i am getting a "run-time error 1004: Method Range of object worksheet failed" on the following line:
wsin.Range(Cells(i, 2), Cells(i, 7)).Copy wsout.Range("I" & lr)
Ill look into it after my meeting... What is the value of lr at that point?
Thanks Arkadi - I am unsure how to check what the value of Ir is at that point - if you are able to let me know I will happily check it out.
When the error comes up, you hit debug, then hovering the mouse over a variable usually reveals its value... you can also highlight it, right click, and select "Add Watch" which will add it to the watches window (bottom right usually).
Nevermind... I figured it out... to copy from the INPUT sheet, it needs to be active. Just in case a different sheet is active, I have added a variable to record the active sheet which is then re-activated at the end.
Please Login or Register to view this content.
Arkadi - thank you so much! You're a star!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks