Conditional formatting adn data validation

As the conditional formatting formula use:

=ISTEXT(A1)

As the source for the drop down list use a formula like this:

=IF(ISTEXT(A1),D1:D3,#N/A)

Where D1:D3 are the selections available for the drop down list.

For the conditional formatting...

Select cell A2

Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format

Enter this formula in the box below:

=ISTEXT(A1)

Click the Format button
Select the desired style(s)
OK out

For the data validation...

Select cell A2

Goto the Data tab>Data Validation
Allow: List
Source: =IF(ISTEXT(A1),D1:D3,#N/A)

Where D1:D3 (or whatever range you want to use) are the selections available for the drop down list.

You need to amend Tony's instructions to check if A1=Apple instead of checking if A1 is text.

First, select A2, then select 'Data Validation' on the 'Data' tab. Select 'List' and enter this:
=IF(A1="Apple",$A$5:$A$9,#N/A)
(replace $A$5:$A$9 with the range where your list is)
Click OK - if A1 doesn't yet contain 'Apple', you will get an error message saying 'The Source currently evaluates to an error. Do you want to continue?' - click Yes.

Now, with A2 still selected, click 'Conditional Formatting' on the 'Home' tab and select 'New Rule' then 'Use a formula to determine which cells to format'. Enter this formula:
=C1="Apple"
Click the 'Format' box and choose red fill.

That should do what you want - here's a file with it working: CF and DV on drop-down list _ for JulijaKT.xlsx

Originally Posted by JulijaKT

Everything looks fine, except that:
I write apple and choose Jonagold
Then I write Banana (in A1) and A2 stays ,,Jonagold" - it should be blank...
Is it possible to make it blank?

That would require a VBA event macro.

I'm not much of a programmer so someone else will need to help you with that.

Thanks for the feedback!

Sorry I haven't been on for a day or two - some wind damage at the house to deal with

I've got it working for you, but it does need VBA as Tony said, so this will only work if macros are enabled.

Open the VBA Editor by pressing Alt-F11, then right-click on 'Sheet1' and select 'View Code'. Paste in the code below.

Please Login or Register  to view this content.

This looks to see if cell B9 changes (which is what B14 looks up, which is what the drop-down in B15 checks). If B9 changes, it blanks B15.

I'm not sure why the drop-down list was showing your formula instead of the correct list, but I re-did the data validation and it now seems to be working.

Here's your file back - I think it now does what you want it to: 2015 11 09 lentele intranetui priėmimas _ with B15 clear macro _ AS.xlsm

You're welcome and thanks for the rep (again).