Macros: Get color of the cell in first row

Question

I have an excel with red and green colors in the first row. I want to check that if the color in the first row of the active cell is red or not. If it is red, display message "Required field." and get the focus back on the same cell. The colorindex of red is 48.

Example Algorithm:

If ActiveCell is C2 and it is empty, display message "Required field" and focusCell = C2
If ActiveCell is G2, do nothing

Thank you for your help.

Possible duplicate of [Excel formula to get cell color](https://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color) — Nicolás Carrasco-Stevenson, Apr 09 '18 at 06:33
First of all `ColorIndex = 48` is grayish, not red. Second, what you wrote in your post description and your "Algorithm" are not the same. Third, if you want to check the color of `ActiveCell` you don't need to retain focus on it, as it is the `ActiveCell` — Shai Rado, Apr 09 '18 at 06:43
I think this is more about _behavior_ depending on the header cell color (not about getting the color in a cell, as in the misleading title and the mentioned duplicate) — paul bica, Apr 09 '18 at 07:50
Please know that I am new SO as evident from my reputation. Instead of marking it as duplicate and saying it is misleading, I would encourage tips on how to improve and getting a solution for the problem. Nobody is perfect the first time. You weren't perfect when you started either. But I still thank everyone of you for your input. — , Apr 09 '18 at 08:28
I was trying to be supportive (you could change the title to something like "_Excel VBA - Restrict users to fill out required fields based on header color_"); Is the answer I provided bellow working for you ? — paul bica, Apr 09 '18 at 09:48

score 0 · Answer 1 · answered Apr 09 '18 at 10:34

Place this in a module:

Function CellColor(ws As Worksheet, irow As Integer)

        Dim lcol As Integer
        Dim icol As Integer
        Dim ccell As Range

      'Gives last populated column in the row
      lcol = ws.Cells(irow, Columns.Count).End(xlToLeft).Column

      'Loops through each cell until last populated column
      For icol = 1 To lcol

          Set ccell = ws.Cells(irow, icol)

              If ccell.Interior.ColorIndex = 3 Then
                  MsgBox ccell.value & "is Red"
              ElseIf ccell.Interior.ColorIndex = 4 Then
                  MsgBox ccell.value & " is Green"
              Elseif ccell.value = "" then
                  ccell.value = "Required Field"
              End If

      Next

    End Function

Example to call the Function on Sheet1 and first row:

Call CellColor(Sheet1, 1)

If you would like to change the colors but don't know what is the ColorIndex, just select a cell on excel, paint with the color you would like then run on your VBA Immediate Window (ctrl + G):

Msgbox activecell.Interior.ColorIndex

Either activecell or range, and then .Interior.ColorIndex will retrieve the ColorIndex for you to use in the Function.

If you would like to know the address of the green cell or red cell you can substitute:

MsgBox ccell.value & "is Red"

For

MsgBox ccell.address & "is Red"

Good luck!

Macros: Get color of the cell in first row

1 Answers1