We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Change Excel Cell Color when doc is saved

sullisnyc44
sullisnyc44 asked
on
Medium Priority
292 Views
Last Modified: 2012-05-11
I have an excel template that I use for a purchase order form.

I would like to highlight the cells that a user must fill in on this form.

when the document is saved for the first time I would like the cell color to be removed.

thanks
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
If the user saves the file before completing all cells, the highlighting will disappear and the form could then be incomplete.

Therefore, the above would need a check that all cells have been completed.

Alternatively, rather than filling in the form directly, setup a data entry sheet (a simple list would suffice) with a button that runs a routine (including a check) that populates the form and then copies that sheet to a separate file.

Cheers
Rob H
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
This spreadsheet is a custom content type in a SharePoint Doc Library and therefore is an .xlsx. Will this code still apply?

Although I agree that some items may be 'incomplete', they are not required.

My goal is simply to make the fields they need to fill in blatantly obvious.

Ideally, I really only want this code to execute once.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
ah. d'oh.

thanks.

one more question... I have a cell that is a particular color. How do I find out what excel thinks that color is so I can set that one cell to that color?

Author

Commented:
also - will this work with merged cells?

Author

Commented:
I'm getting a runtime error...

 
If SaveAsUI Then
       Sheets("Purchase Order").Range("C12,D12,C13,C14,C15,C16,D19,F19,H19,D21C26,D26,E26,G26").Interior.ColorIndex = xlColorIndexNone
    End If

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
ah - named ranges work.

Author

Commented:
Well I have the RGB values if I select the properties...

what would the colorindex syntax be though?
Red - 184
Green - 204
Blue - 228

Selection.Interior.Color = RGB(184, 204, 228)

Is that correct?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks for the last clarification - I didn't even notice color vs colorindex and I was getting a runtime error.

Thanks! It works like a charm now.

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.