unprintable highlighting

[Wilco()]

Hi

I have created a workbook containing a few Lists using Data Validation. Which results that the dropdown icon is only visible when the cell is selected.

I want to make it user friendly. And like to know if I can highlight the cells in some way to make it clear where the dropdown is located.

Is there a way to do this?

 

[Mike H..]

Hi

I have created a workbook containing a few Lists using Data Validation. Which results that the dropdown icon is only visible when the cell is selected.

I want to make it user friendly. And like to know if I can highlight the cells in some way to make it clear where the dropdown is located.

Is there a way to do this?

Hi,

Home tab | Find and select | Data validation to select them all and then click a colour.

 

[Wilco()]

Thanks, that is nice to know. However, I want the user to be able to see this when opening the sheet. It may be colourful on screen, but when printing I do not want it to be shown.

 

[lohithsriram]

Hi Wilco,

Use Mike H's idea to select all the datavalidation cells in the Excel worksheet and add the Conditional Formatting for all the cells.

Choose all DataValidation Cells
Conditional Formatting -> Format only Cells that contain -> Format only cells with "No Blanks"

This should show the color on the worksheet when user opens it up but will not print. Also, if you require to show a different color or set the cell background to nothing, you can add another formatting rule for Non-Blanks.

 

[Deepak]

Make colorful your sheet as you want & this code will only print Black & White.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = True
With ActiveSheet.PageSetup
    .BlackAndWhite = True
    Application.EnableEvents = False
    ActiveSheet.PrintOut
    Application.EnableEvents = True
    End With
End Sub

 

[bobhc]

Good day Wilco()

Can't you just change the print settings to print black&white?
Setup/page setup/black &white

 

[Wilco()]

Printing in black&white is not a possibility. The worksheet I created is meant for invoicing (including company logo in colour).

Or is there another option than using the List option in Data Validation to select customer and product?

 

[KenU]

It may be more subtle than you would like, but you could add a comment to those cells. That would put a red triangle in the corner of the cell.

 

[Wilco()]

@KenU simple but I like it

Though, if someone does have another solution feel free to share. I am going to use KenU's suggestion for now.