Highlighting overwritten cells - conditional formatting

gwoolley0302

Board Regular
Joined
May 13, 2014
Messages
57
Hi all,

We share an excel template with our network for them to input quarterly monitoring data with us. Some fields we attempt to restrict through drop down lists. These often get overwritten.

So my question is whether we can use a tool to show the user that they've overwritten a dropdown list cell? (without a macro)

I thought maybe conditional formatting could highlight the cell in red if they overwrite the dropdown list?

We also get a similar issue when data is not entered in the format we would ideally like. Again I wonder if we can use a tool to prompt the user that the cell is incorrect (i.e. a red cell indicates it's been inputted incorrectly)

Thanks, Greg
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi Sanjay, Thank you. That works well when a user enters text into a cell. However, when a user copies and paste values, the error alert does not work. Any ideas how to solve the issue when a user is pasting content over a dropdown list?

Thanks, Greg
 
Upvote 0
Che this and revert - Here DVList is the List used for making Drop Down List

Book1
ABCDE
1List
2Test 1Test 1
3Test 3Test 2
4Test 8Test 3
5Test 5Test 4
6Test 5Test 5
7Test NewTest 6
8Test 7
9
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B:BExpression=AND(NOT(COUNTIFS(DVList,B1)),B1<>"")textNO
Cells with Data Validation
CellAllowCriteria
B1:B9List=DVList
 
Upvote 0
Thanks Sanjay, I have tried this in the below sheet, but it doesn't seem to be working.

1) I'm a little confused on the use of "B1"

2) Occasionally it has worked, but when you then change the overwritten cell to a dropdown value, it (incorrectly) stays in the style of the conditional formatting . Can it revert back to no conditional formatting if corrected?

Thanks for your help.


Picture1.png
 
Upvote 0
I'm a little confused on the use of "B1"
share the formula you are actually using for Conditional Formatting

Also, Have you created a Namaskared range for I2:i11?

Further, if you copy paste in a cell - Conditional Formatting Rules for that cell may also change as per the source cell.
 
Upvote 0
Thank you Sanjay.

The formula used is =AND(NOT(COUNTIFS($I$2:$I$11,B1)),B1<>"") (Not currently a named range no - is that needed?)

Yeah I did notice that when copy and paste is done, is alters the range that the conditional formatting applies to - e.g. B1:B150000 becomes B1:B7,B9:B150000.

But copy and paste is the issue we're trying to overcome. We're trying to highlight to users they shouldn't do that.
 
Upvote 0
Check this and revert -

Book1
ABCDEF
1CheckList
2Test 11Test 1
3overwrite0Test 2
4Test 80Test 3
5Test 51Test 4
6Test 51Test 5
7Test New0Test 6
8Test 7
9overwrite
10
Sheet1
Cell Formulas
RangeFormula
C2:C7C2=IFNA(LOOKUP(1,1/(B2=E2:E8)),0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C:CExpression=AND(C1=0,C1<>"")textNO
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top