How would you change each cell coloured grey in 45 sheets with another colour?
Hi community!
I need some help to tackle this issue. I have more than 45 sheets in an excel file where every cell is in grey except for some in white (where users fill in data).
I was wanting to change this "background colour" but there is no way to do it manually. It would take me lot of days and I would like some different colours to test user experience.
Do you know any macro that can take a sheet (or many), select grey cells and colour them again?
Best,
Gerónimo
The CTRL+H Find/Replace tool can do this for you.
1) Press CTRL+H to open the replace window
2) Make sure the expanded OPTIONS are visible
3) Under FIND WHAT, click on Format... drop down and use one of the options provided to correctly select the grey color you're replacing
4) Under the REPLACE, use the Format... drop down to select your new color
5) Within: Workbook
6) Replace All
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
You can use find and replace to search for specific cell fill color.
In find & replace panel, hit options to expand. Then choose fill format color and search.
It will list all cells that has specific fill. You then change format to replace with and replace all to do it in one shot.
You can also set "Within:" property to "Workbook" instead of just the sheet.
0.JPG
Thanks a lot!
I tried in one sheet (and I honestly didn't remember the format option in Ctrl-H).
Unfortunately, I am getting a beautiful "Excel stopped working..." and it closes down. In the repaired version only some got painted =S
Would you do something else?
Best,
Gerónimo
Reboot your system. Don't start anything except this one workbook.
Disable all macros (Developer > Design Mode)
Now try again, maybe better results.
What do you think about something like this? I still don't know how to set up the colours in vba.
Please Login or Register to view this content.
I finally came up with this. The problem with Ctrl-H is that it has activated the ScreenUpdating. That is why I think that it crashes.
Many many thanks! What do you think?
GerónimoPlease Login or Register to view this content.
See if this works for you...
Replace the color in blue with the correct gray color you have on the sheets.Please Login or Register to view this content.
To know the exact gray color number, say you have gray color in the cell A1, then in immediate window type Debug.Print Range("A1").Interior.Color and hit enter, that will give you the exact color number.
The code will replace all the gray color with Yellow, replace yellow color in the code as per your requirement.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks