Data Validation/Sheet & Cell protection

[Zach]

Ok I've been working with upper management to build a spreadsheet to track our communities (I'm in the home building industry). Well as upper management usually does they have decided to go a complete new direction. Before I had a macro that would format cells when the job was complete (view attached file).

THIS IS WHAT THEY WANT NOW:
1. They want all the cells to only allow "N/A, Complete, or the date" in MM/DD/YYYY format. I've worked with data validation but I don't even know if it's possible to have 2 constant variables (n/a & complete) and a non-constant variable like date in data validation unless I list every single day out.

2. They want to proctect the yellow cells to alway remain yellow reguardless of what the macro does. They want all the white cells to only format to blue, pink or stay white. I don't even know if you can do this, is there a way to limit what colors a cell can turn?

This is far beyond my knowledge, if anyone has any answers or advice please let me know.

 

[Luke M]

For the first part, you could write a data validation rule like this:
=OR(D4="N/A",D4="Complete",AND(D4>=36526,D4<=80000))
The last bit is a check to make sure it's a number (aka, a date), and that range is from Jan 1, 2000 to Jan 11, 2119 (which hopefully is long enough).

For the 2nd, thankfully it looks like you're using just a plain yellow formatting. I think we can just modify the "Complete" macro to this:

Sub Job_Complete()
'
' Job_Complete Macro
' Job Complete; created by Zach Prilliman 10/11/13
'
 
'
    With Selection.Interior
        If .Color = vbYellow Then
            'Can't change yellow cells
            MsgBox "Can't touch this.", vbOKOnly + vbCritical, "Hammer Time"
            Exit Sub
        End If
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
End Sub

 

[Zach]

That works great thanks, I didn't know you could validate a number range like that.

Is there a way to limit what color a cell can be formated too? Like it's a white cell and they want it to only be allowed to be either light blue, light pink or remain unchanged/unformated?

 

[bobhc]

I've been working with upper management

From bitter experience the words upper and management should never be used in the same sentence.....:D light pink, now you are having a laugh. I thought the people you are working with are in the building industry, Light pink...

 

[Zach]

Haha fair enough, I should have said light red. They just want a way for people to mark dates that need to be looked into. And if I give 10 people an option to pick a color I'll get 10 different colors, so they are trying to make less options for everyone to help lessen the confusion.

 

[Luke M]

You can limit it, but it becomes cumbersome. In a nutshell, you create a macro for each button designation, protect the worksheet, and then have the macros unprotect the sheet, format the cell, and then re-protect the sheet.

If you can convince the managers(PHBs), I think a better dashboard design is to have a controlled drop down, and then use conditional formatting based on the selection to color the cells.

 

[bobhc]

Luke M

pointy-haired boss, yep just about right....