?
Solved

Change Excel Cell Color when doc is saved

Posted on 2011-04-18
13
Medium Priority
?
284 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
0
Comment
Question by:sullisnyc44
  • 7
  • 5
13 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35419635
In the ThisWorkbook module, assuming it's a template and therefore needs a SaveAs:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI then
   Sheets("some sheet").Range("A2,A4,B5,C17").Interior.Colorindex = xlcolorindexnone
End If
End Sub

Open in new window


for example.

0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 35423710
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
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 2000 total points
ID: 35423758
It would actually be even simpler to add conditional formatting to the cells that only highlights them if they are blank.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sullisnyc44
ID: 35424227
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.
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 2000 total points
ID: 35424411
It would have to be an xlsm rather than xlsx to contain code.
0
 

Author Comment

by:sullisnyc44
ID: 35424516
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?
0
 

Author Comment

by:sullisnyc44
ID: 35424526
also - will this work with merged cells?
0
 

Author Comment

by:sullisnyc44
ID: 35424571
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

0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 2000 total points
ID: 35424581
You can use:
msgbox activecell.interior.color

to get the color value (in RGB values).

Note, my code assumes the cells are already coloured and simply clears that the first time the book is saved.
0
 

Author Comment

by:sullisnyc44
ID: 35424585
ah - named ranges work.
0
 

Author Comment

by:sullisnyc44
ID: 35424649
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?
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 2000 total points
ID: 35424662
Yes - if you want to use RGB, then use Color rather than ColorIndex.
0
 

Author Comment

by:sullisnyc44
ID: 35424886
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.

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question