Discussion on:

Highlight Excel list values when they change

Those commands are not available in Excel 2003....or the version I have in work

I think it's better use range A2:A35 with the formula =A1 not equal A2 so we have formatted A2 as a title of a group.

I'd select A2:A35 and use the "Cell Value Is" option, then "not equal to" and then "=A1"
Specify required format and accept as in main article

JB

I use this simple Condititional formula in all selected cells:
=IF (A2=A1, True, False)
and set the Font Color to Dark Gray.

Next, apply the format for A2 to all other values in Column A.

This returns easily identifiable Duplicates and the 'original' values remain Black (or whatever the user's default color is for a column of values).

Russ

This problem is an interesting one. If you think about what you are doing it all makes very clear sense.

By selecting the range A2:A35 and comparing A2 to A3, you are saying, "if I am the same as the value below me, I am not unique." This means that the last value is considered the unique value.

By selecting the range A3:A35 and comparing A2 to A3, you are saying, "If I am the same as the value above me, I am not unique." Now, the first value is going to be considered the unique value.

However in this example, your range doesn't include the entire dataset. You'll notice that the first "Actuaries" is not highlighted and it should be. Since the 1st row is a header row, it should be different from the data so you can do the comparison to the first row, and still select the range of A2:A35 so the complete dataset is included. Now the first "Actuaries" will be highlighted as well.

This article only deals with how to do this with a sorted list. If your list is unsorted, you can use the following formula in your conditional formatting:
=countif($A$1:A2,A2)=1

Notice the dollar signs ($).

This is going to count the number of occurences of the value in the currrent cell from the 1st cell in the range. By saying it is less than 2, it is saying the value is equal to 1, you are saying it is the first unique value - so highlight it.

I use conditional formatting to help me during the development of my spreadsheet solution. One example is with drop-down lists. I usually have my list items defined in specific cell ranges, rather than hard-coded into the data validation range box.

For example, I may have cells E1 to E3 contain list1, list2, list3. I will give E1..E3 a range name. (This is important if you want to access the range from other sheets.) This gives me the flexibility of adding new list items as my development proceeds. The problem I have is when I decide that list1 should now be list4.

While the drop-down list shows up with the new item, I still have a bunch of cells pre-populated with list1. What I do is tag some conditional formatting to highlight values when they become obsolete. Simply follow the instructions above but use a formula such as =ISNA(VLOOKUP(A1,ListRange,1,FALSE)).

The VLOOKUP function tries to find the cell value into the cell range used for my drop-down - ListRange. If the VLOOKUP doesn't find it, it raises a #N/A. The ISNA function will then return TRUE, triggering the conditional formatting.

Now all my cells with obsolete values get highlighted, reminding me to do a search & replace.

Conditional formatting is a great tool for the developer, not just the end-user.

Apply to A:E
Formula: =$A1OFFSET($A1,-1,0)
Format: Yellow

Should have been
Apply to A:E
Formula: =$A1 NE OFFSET($A1,-1,0)
Format: Yellow .

Works for me!

Seems like a lot of work when you just chose the wrong formula to start with.

If you use conditional formatting for the first time, formulas like = (A2 A3) shall be easier for you.

This method will bring you error if you delete Row 3 for deduplication because cell A3 is deleted. I would suggest using =(A2 offset(A2,1,0)) for cell A2; or =(A3 offset(A3,-1,0)) for cell A3. This would not cause you error even you try to delete duplicated rows.

Lastly, you may want to use "Advance Filter" to etract unique records. This will not affect your original data source.

I have been struggling with what should be an easy problem?!?!? I developed an employee schedule and want the work hours shaded to show coverage for each day. I am using conditional formatting for each cell but want it to change color only if that cell has text in it and the first cell in that row has text in it. Sounds simple but I can seem to get it right!!! Any help would be greatly appreciated.!!

In my solution, I assume that Row 1 is used for Headers and that Rows 2 & greater are for data entry.

I further assume that Column A contains text, perhaps employee names or weekdays, and that Columns B & greater are for numeric data entry.

In my suggested formula, I arbitrarily use cell B6 as the one into which data will be entered to test the validity of the following Conditional Format formula:

=IF(AND($A6>0,B6>0),TRUE,FALSE)

$A provides an Absolute Reference to Column A whereas B6 is a Relative Reference so you can Copy the format for Cell B6 and apply it anywhere on the worksheet.

When you select a Pattern to conditionally shade populated cells, I recommend that you also apply an Outline Border since the shading of cells obliterates default gridlines during Preview and Printing.

Of course, there are other formulae that will perform what you want, but this one is straightforward and uncomplicated.

Russ

Without the IF

You are correct.
AND($A6>0,B6>0) works just fine.
I guess we all have more to learn...
Thanks, Russ

Thanks for the help. Problem solved!!

assuming your input cell is C3, use Formula is... option and enter formula as
=LEN($A3)*LEN($C3)
set formatting options and away you go!

NB this checks for any entry, if you specifically only want to find TEXT use ISTEXT()

It just tests two but it would be easy to expand to test more columns.

=AND(NOT(ISBLANK($A1)),NOT(ISBLANK(A1)))

The first test uses absolute column reference to test the cell in column A in the row of the cell being formatted. The second tests the cell being formatted.