Required Field Check > Based On Color Index


New Member
Jun 29, 2017
Hello -

Thank you for any help in advance. I'm still trying to learn VBA and have been trying to figure this out for a while - and need some help.

I am trying to make certain fields required before the user can access the print button I created using the form tools on a specific worksheet. To reduce headaches moving forward, I was thinking about creating a macro that would check a specific worksheet based on a predefined "RGB ColorIndex" for required fields. I'll color all required fields this predefined color, which will require input prior to the user being able to access the print/submit button(s) I created within this specific sheet.

My thoughts are that this will help me later if changes are made on the worksheet (e.g. new fields added, reorganizing fields, or not requiring certain fields any longer) - so all I will have to do is ensure a required cell is colored correctly which would make it required automatically per the VBA code. This would save a lot of time, rather than defining an array of specific cells that will have to be manually updated every time a change is made to the worksheet.

Does anyone have any ideas on how to achieve this?

In summary, the logic in my mind is below. I just don't know how to put this into a working VBA script.

> Check range on worksheet named "X" for cells with specific color index.
> All cells that are equal to the color index predefined for this specific worksheet are required, so check these fields for input (not blank or empty).
> If cells with predefined color index are empty, then hide/disable the print button I created on the worksheet and notify user in message box which cells require input.
> Else, if some required fields have input, but not all required fields, then notify user in a message box stating the fields that require input.
> Else, if all required fields have input, then show/enable the print button.

I'm guessing it would be okay to put this under the Worksheet_Change sub, but feel that could cause the error message to popup every time a user begins filling out the blank worksheet with information - becoming annoying and inefficient. Is there a better worksheet specific sub to put this under?

Hopefully, this makes sense on what I am trying to achieve. Thank you community for all the help and assistance! :)

- RD

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).


Board Regular
Mar 5, 2016
Here's something I came up with. It uses the Change event as you had mentioned, but instead of showing a messagebox with the missing values, it uses 2 colors to show good or bad entries. This way, the popup doesn't happen every time, but the user would have to know that the one certain color (e.g., yellow) needs to have a value before the button can be enabled.

There is also a variable called lookupRange that you can taylor to include all of the cells that could be colored. It's meant to be a subset of all the cells on the spreadsheet so that searching for the colored cells doesn't take forever.

Obviously, you'll have to change what I've hard coded to match the colors, lookupRange you need. Also, change the name of the CommandButton to whatever you have.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim coloredCells As Range, iSect As Range, showButton As Boolean
    Dim colorIndexEmpty As Integer, colorIndexNotEmpty As Integer
    'Find colored cells
    '   lookupRange is a range to search for colored cells
    '        This should be big enough to include the colored cells but
    '        not too big to take forever to find
    '   colorIndexEmpty is the color of the cell if empty
    '   colorIndexNotEmpty is the color of the cell if there is a value
    '  if you need to determine the colorIndex values, select a cell that has
    '  the color you're looking for in the spreadsheet, then in the VBE Immediate window,
    '  type the following and then hit ENTER:
    '     ?activecell.Interior.Colorindex
    colorIndexEmpty = 6 'Yellow
    colorIndexNotEmpty = 4 ' Green
    Set coloredCells = ReturnColoredCells(Range("A1:E20"), colorIndexEmpty, colorIndexNotEmpty)
    If coloredCells Is Nothing Then
        Exit Sub
    End If
    'Check to see if changed cell is one of the colored cells
    Set iSect = Intersect(Target, coloredCells)
    If Not iSect Is Nothing Then
        showButton = True
        For Each iSect In coloredCells
            If iSect.Value <> "" Then
                iSect.Interior.ColorIndex = colorIndexNotEmpty
                showButton = False
                iSect.Interior.ColorIndex = colorIndexEmpty
            End If
        If showButton Then
            Me.CommandButton1.Enabled = True
            Me.CommandButton1.Enabled = False
        End If
    End If
End Sub

Function ReturnColoredCells(lookupRange As Range, colorIndexEmpty As Integer, colorIndexNotEmpty As Integer) As Range
    Dim c As Range, returnRange As Range
    For Each c In lookupRange
        If c.Interior.ColorIndex = colorIndexEmpty Or c.Interior.ColorIndex = colorIndexNotEmpty Then
            If returnRange Is Nothing Then
                Set returnRange = c
                Set returnRange = Union(returnRange, c)
            End If
        End If
    Next c
    Set ReturnColoredCells = returnRange
End Function
Last edited:


MrExcel MVP
Jan 15, 2007
You could put this in the sheet's code module.
Adjust the range in RequriedFieldsFilled to include any possible cells that might be required.
Adjust the name of the button in the Change event.

' in sheet's code module

Private Sub Worksheet_Change(ByVal Target As Range)
    Me.Shapes("Button 1").Visible = RequiredFieldsFilled
End Sub

Public Function RequiredFieldsFilled() As Boolean
    Dim oneCell As Range
    RequiredFieldsFilled = True
    For Each oneCell In Me.Range("A1:A100"): Rem adjust
        With oneCell
            If .Interior.Color = vbYellow Then
                RequiredFieldsFilled = RequiredFieldsFilled And (CStr(.Value) <> vbNullString)
            End If
        End With
    Next oneCell
End Function
Then put this code in the ThisWorkbook module, to prevent the user printing with methods other than your button.
(Adjust the name of the sheet as needed)

' in ThisWorkbook code module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If Not Sheet1.RequiredFieldsFilled Then
        MsgBox "missing some data print canceled"
        Cancel = True
    End If
End Sub


New Member
Jun 29, 2017
Hi shknbk2 and -

Thank you both for the reply, and for taking the time to generate the coding solution for this issue. You both are awesome and I sincerely appreciate it. Also, I apologize for the delayed response. I just got back in town and have been catching up on emails.

I'll give these a try and reply back letting you know how they worked, or if I have any questions - if any arise. Thank you both again and have a great week. Talk soon.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...