Highlighting cells that have Input message data validation

Question

Hi

Any tips on how to highlight cells where an input message in the cell validation (eg through conditional formatting or other way)

Eg in the cell "Test" below, I'm looking for an easy way alert that the cell has a input message on it

Thanks

Answer 1

Hello SP#7,

Thank you for coming to the forum and I will be happy to assist in the best way I can.

For me, the whole point of the message is to come up when they click on the cell. There is no way to make the cells have a different color using conditional formatting that I know of. You will have to color them manually. But i think that is an overdo of what you have already achieved with the text input message.

You can go further and do data validation on the cells to make them accept only a type of input. https://support.office.com/article/29fecbcc-d1b9-42c1-9d76-eff3ce5f7249

If there is anything else you need let me know.

If none, have a wonderful rest of your day.

Kind Regards Fuad

Answer 2

Hi,

step1

Save  your Workbook with extension .xlsm (macros enabled workbook)

Step2

2a) press ALT+F11 to open Visual Basic

2b) from the ribbon, select: Insert > Module and paste the code below on the right 

2c) Press ALT+Q to Close Visual Basic

step3

To run the macro, press ALT+F8, 

select '**Search_for_Validation'**from the list and click the run button.

or

add a button and assign the  vba macro

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Sub Search_for_Validation() '<< Start Line

'## 22-10-2022 AnastasiosGR ##

Dim ws As Worksheet

Set ws = ActiveSheet 'change as needed

Dim rr As Range

Set rr = ws.Range("A1:Z100") 'change as needed

Dim vrr As Range, r As Range

Dim n1 As Long, n2 As Long

n1 = 0

n2 = 0

On Error Resume Next

Set vrr = rr.SpecialCells(xlCellTypeAllValidation)

On Error GoTo 0

If vrr Is Nothing Then

MsgBox "no validation cells"

Exit Sub

Else

For Each r In vrr

n1 = n1 + 1

If r.Validation.InputMessage <> "" Then

n2 = n2 + 1

r.Interior.Color = vbYellow '<< interior color yellow

End If

Next r

MsgBox "Total Validation Cells=" & n1 & vbNewLine & "Cells input msg = " & n2

End If

End Sub '<< End Line

Answer 3

The easiest way to find and highlight all cells with Data Validation is to Press F5 and select Special... in the bottom left-hand corner.

and then choose Data validation (All or Same), OK.

Now format the selected cells with the color of your choice.