# Conditional Formatting Of Duplicates Across Multiple Cells

Feb 25, 2014

I use the conditional formatting of duplicates within a column quite often. Now I am trying to highlight duplicates based on multiple cells in the same row. I can't seem to get a formula to make that work. I have four cells in separate columns but the same row, for example H2:K2, H3:K3, H4:K4, etc. I would like to highlight the four cells in that row if there is a duplicate of all four cells in another row. So if one, two or three cells are duplicates in the same column I do not want to highlight any of the cells, it should only highlight the group of four cells if all four cells are identical in another row. Pretty much the same way the default highlight duplicates works, just the value is spread across four cells.

## Conditional Formatting Duplicates Across Multiple Sheets

Aug 13, 2009

Conditional formatting duplicates across multiple sheets. I have been using the formula

## Conditional Formatting Multiple Cells

Jan 28, 2014

I need to conditional format a cell based on it's current value and a value in another cell.

For example:

Each person would have a different target. So if Steve has a figure of over 20 his highlights green, Paul 12 etc.

However each person would not appear in the same row each day.

The Conditional Forumla of =(A1="Steve.Smith")+(C1>="20") works, but then applying to all cells messes it up a bit.

25

Paul.Smith

14

Ian.Smith

15

Jim.Smith

20

John.Smith

18

## Conditional Formatting In Multiple Cells

Oct 25, 2007

I am having a problem setting conditional formatting for multiple cells.
The first condition of the conditional formatting works for the cells but the second condition does not. There is probably an easy fix for this but I can't seem to find it.

## Applying Conditional Formatting To Multiple Cells?

Apr 25, 2012

If you have a range of cells with values in them (Q2:AZ2), you can use conditional formatting to identify the top x% of the group and it would essentially treat each cell in comparison to the group. I need to identify if a cell in the group is greater than 3*Stdev(Q2:AZ2). Do I have to create conditional formatting for each individual cell or is there a way to apply a function to the group that would recognize each cell the same way that the canned conditional formatting rules work?

## Conditional Formatting If Multiple Cells Contain Same Text

Dec 30, 2012

I want to conditional format based on all of the following cells contain the same text as AT19 or not.

A19 H19 O19 V19 AC19 AJ19

What is my formula?

## Conditional Formatting Of Cells In Multiple Sheets

Sep 6, 2013

I have twelve sheets that each have a total score on cell G10 that I want to have copied to a thirteenth sheet in a specific column (F5-16). What would I have to do to make that happen? The G10 cell is an auto-summed cell, if that makes a difference.

Also, is there a specific way to create formulas?

## Conditional Formatting / Add Duplicates

Jul 10, 2009

I need to create a formula that will look at a p/n find its match and add the value from all matching p/n together. After this is done, all of the line items that matched I need them to turn red. But I do not want the first p/n to turn red. It is better explained in my example.

## Conditional Formatting/Duplicates

Mar 19, 2009

My background is many years of Access VBA. From time to time, I'm called upon to work in Excel. I am not that familiar with the library.

I'd like to know if it is possible to do the following:

Examine Column 1 and, for each like value, examine Columns 2 and 3 for duplicate values. Highlight or conditionally format the duplicates in Columns 2 and 3 (again, for each like value in Column 1).

Being that I've coded Access applications for quite some time, I am sure it can be done...I just don't yet know exactly how. I've searched the archive for and found some examples which deal with duplicate data, but have found none yet which quite fit my scenario.

I've found sample code which enforces rules and conditionally formats cells when new data is added. It works fine and I understand it. I'm hoping the kind soul who might help me with the above can explain how I might do both things on the code page.

I assume the Worksheet_Change Event would contain the "enforcement" code.

## Conditional Formatting - Duplicates

Aug 4, 2009

Below is some sample data

Column A is the START date/time

Column B is the END date/time

What I'm looking at doing is high lighting any cell where the START and END dates clash.

Is this possible via CF?

If possible highlight the START cells where a clash occurs.

AB1STARTEND2Fri 03/07/2009 11:30 AMFri 03/07/2009 4:30 PM3Wed 08/07/2009 9:30 AMWed 08/07/2009 10:30 AM4Fri 10/07/2009 1:30 PMFri 10/07/2009 1:00 PM5Wed 08/07/2009 9:00 AMWed 08/07/2009 11:30 AM6Tue 07/07/2009 11:30 AMTue 07/07/2009 1:00 PM7Thu 09/07/2009 11:30 AMThu 09/07/2009 1:30 PM8Tue 07/07/2009 1:30 PMTue 07/07/2009 4:30 PM

## Conditional Formatting For Duplicates

Jul 29, 2006

I can't follow the directions on http://www.ozgrid.com/Excel/highlight-duplicates.htm to make the conditional formatting work. The formula they say to use is = COUNTIF(\$A\$1:\$A\$100,A1)>1. I've changed the range to read=COUNTIF (\$F\$10:\$G\$999,F10)>1 and have applied a format change of the text color. It is obvious I have duplicates but they are not changing color. I have the range highlighted before I go to conditional formatting.

## Conditional Formatting For Cells Which Have Multiple Date Info

Feb 10, 2009

I need for coloring "Expected Delivery Date" column corresponding with "Control Date" and "Control Result. But I want to correspond with the cells' last content of "Control Date" and "Control Result" But first of all I want "Control Date" should be flashed 10 days ago of it's date... (it's already done on my excel file)

Then
I want "Expected Delivery Date" as green when
- "Expected Delivery Date" =< "Control Date" and "Control Result" =Y (Means Control is Ok on due time)

I want "Expected Delivery Date" as red when
- "Expected Delivery Date" > "Control Date" and "Control Result" =Y (Means control is ok not on due time)

- "Expected Delivery Date" > "Control Date" and "Control Result" =N (Means control ok is not given, delayed...)

## Excel 2007 :: Conditional Formatting On Multiple Cells?

Apr 17, 2012

I'm using 2007 and want to add conditional formatting to multiple cells.

Say I have 3 columns and 10 rows of data (A1:10 ; B1:10 ; C1:10). I want to add an icon set to everything in columns B and C based on a comparison with the figure on their immeidate left. So B1 is compared to A1, C1 to B1 etc.

When I do it though it compares everything to A1 (formula in the Conditional Formating is =\$A\$1 and it won't let me remove the \$'s). It looks to me like I can't do it and have to add the formatting to every cell indiviually (which is quite a lot).

## Copying Conditional Formatting Formulas To Multiple Cells?

Jul 9, 2013

I have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.

Sample: =IF(\$C\$5/\$C\$295>\$R\$5,TRUE,FALSE)

I want to copy to \$D\$5/\$D\$295>\$R\$5,TRUE,FALSE) without having to paste the new formula in each time.

## Conditional Formatting Highlight The Duplicates

Oct 4, 2008

I have a worksheet that has times based on 7 minute intervals. If I post 10:37 AM in cell b5 then again in cell b6 I would like b6 to highlight.

I'm trying to use countif(\$b\$3:\$b\$15,b2)>1 to highlight duplicate times in column B. For some reason it will not work for me. Should I use something different?

## Conditional Formatting Formula To See Duplicates

May 26, 2007

I know it can be done cause I've used it before but I can't seem to make it work this time. I want to use the conditional formatting to compare two ranges of cells within one worksheet I know it's something like =match(A2:C12) the two ranges are sitting one on top of the other. . .

## Identifying Duplicates In A List Using Conditional Formatting

Nov 30, 2009

I would like to identify duplicates in a list using conditional formatting in Excel 2007.

I have tried choosing to identify duplicates using the formula that I have found on many threads throughout the message board:

=COUNTIF(\$A\$1:\$A1,\$A1)>1.

This function works up to 15 characters in a cell, but Excel seems to be treating all digits after the first 15 as the same, resulting in a "fuzzy match" where I want an exact match. Many of the values in my list are 18 characters long, in text format to prevent rounding.

I've noticed that Excel treats the 18-characters values the same way when sorting; for example, it treats these two values as the same:

'234567891011121314
'234567891011122413

Is there a way to force Excel to examine those last four digits for the purpose of sorting & identifying duplicates?

## Excel Live Calendar Conditional Formatting With Multiple Cells In Column

Jul 15, 2013

I have designed a live calendar and am trying to highlight selected dates from a column in the calendar. I am able to select single cell in the column and highlight the date in the calendar, But am unable to select multiple dates in the column and highlight multiple dates in the calendar using conditional formatting.

Eg: calender dates are from B5:H9 and multiple dates are in column J10:J13

J column
--------
J10 7/10/2013
J11 7/20/2013
j14 7/29/2013

## Excel 2007 :: Find Duplicates With Conditional Formatting

Feb 20, 2012

Using Excel 2007 and conditional formatting, how would I find duplicates across all fields in the table, similar to the "remove duplicates" tool that in default mode selects all the fields or offers the option to select fields of choice. I need to look at all the fields.

I've tried concatenation, but only with a limited number of fields; larger amounts slow the process to a crawl. If my table is 10,000 records with 15 fields, what process would I use to highlight the duplicates. How about something in VBA?

## Conditional Formatting - Colouring Percentage Of Multiple Cells Based On Date Range

Mar 12, 2014

I am trying to get a row of cells to highlight a percentage based on a date range

Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.

In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.

Name Start Date End Date Jan Feb Mar

Dale 11/01/14 26/02/14 21 days 26 days

I have attached the spreadsheet for an example : Book1.xlsx‎

## Excel 2007 :: Conditional Formatting And Adding Text To Cell Based On Multiple Cells

May 31, 2013

I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.

This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.

I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.

I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.

## Removing Duplicates Between Two Cells Reliant Upon Formatting Of Two Cells?

Jul 30, 2014

Creating a project tracking sheet that is as automted as possible so that people dont have to populate lots. I have managed to get it to create a new sheet and populate according to a filled out combobox. The user then populates a 'milestone' section. When a button is clicked these are transferred to a GANTT chart type sheet and pasted. Before they are pasted the macro checks if the project title exists, if it does not it first pastes the title LEFT INDENTED. Then follows by pasting the milestones CENTRED. If the title exists, it inserts the milestones under the project title by copying and pasting all milestones. Hence, if milestones are regularly added, it will begin to add duplicates of the same milestone. I need to remove these. I would usually be able to do this however there is often the same milestone in numerous projects which I CANNOT remove as it would loose data. I would like a code that says between the cell I was searching for (Rng) and the next cell which is also left indented to remove duplicates. Basically I can't use x1enddown because it needs to stop at the next cell which has the same formatting... i.e. only duplicates to be removed out of the cells which are centred. I would then like it to repear this action for the whole document and move the next left indented cell and do the same.

It feels like a kind of backward conditional formatting is what I am looking for

Here is my code as it stands

Dim FindString As String
Dim Rng As Range
FindString = Range("D2").Value
If Trim(FindString) <> "" Then

[Code]....

## Conditional Formatting - Two Different Fields Affect One Cells Formatting For Date?

Sep 16, 2013

A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.

dust 1.xlsx

## Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?

Feb 9, 2009

I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.

## Conditional Formatting Across Multiple Spreadsheets?

Jun 5, 2014

I'm trying to set up conditional formatting to show when an employee is both scheduled to be working for the current date, and is trained in a particular task.

I have a spreadsheet called "Training" for all of the tasks that employees can be trained in, with the employee names in each row, and the task names at the top of each column. The intersection of an employee's row with a task's column will have an "X" if the employee is trained in the task. This is the spreadsheet that I'd like to be formatted; if the employee is trained and also scheduled to be working, I'd like to the "X" to change to a different color.

There are separate spreadsheets within the same workbook for the schedule. This is a bit more complex since the since each week of the schedule has its own spreadsheet within the workbook; each spreadsheet is named for the Saturday of the week it is for, such as "06-07". I've been able to successfully have other formulas in the workbook auto-adjust to the current week's schedule by using variations on the TODAY function (something like INDIRECT("'"&(TEXT(TODAY()-MOD(WEEKDAY(TODAY(),1),7),"mm-dd"))), but I'm not sure how to incorporate this into conditional formatting. On each schedule sheet, there is a column for each day of the week and a row for each employee. The cell for a given day will contain the employee's schedule if they are scheduled, or it will be blank if they are not scheduled. So, the conditional formatting formula would just need to check to see if the appropriate cell was blank or not.

## Conditional Formatting For Multiple Rows

Jul 8, 2009

I have a spreadsheet of a couple hundred products, their sales over the last 18 months, and then each product's 18 month average monthly sales. What I would like to do is format them so if any of the months' sales are below their respective average, that cell/month with be highlighted. Excel told me that I cannot do this for multiple rows at a time, and I don't want to go row by row for this many products. Attached is a sample file of the first row.

## Conditional Formatting Over Multiple Pages

Dec 17, 2012

I need setting up a conditional rule over 2 pages.

Page 1 displays a list of countries.

Page 2 pulls in the RSS feed from the BBC World News website.

I need to find a way to set a rule that will highlight the country on page 1 if it is mentioned anywhere in page 2.

## Conditional Formatting For Multiple Worksheets?

Oct 23, 2013

I am having an excel workbook consisting of 11 sheets. Can I apply conditional formatting to multiple sheets at the same time?

## Multiple Conditional Formatting With Date?

Mar 31, 2014

Despite 'Google is my Friend' and lot of test, I got a bug in my formula. I'd like to colorize date between (Today + 30) and (Today + 60) The line with the xlsBetween operator, fail...

[Code] ......

Existing solution : I know how to bypass this issue, but it's really an awfull solution : fill all column with orange, and then the Conditional formating will overrun the orange color when nedeed. Works but I don't like it ...

## Conditional Formatting With Multiple Conditions

Jun 10, 2009

I have a spreadsheet that contains a great deal of information, though most if it contains dates. There are some colums that contain the word "NEED" if i don't yet have the date to put in there, and the cell is yellow. What i want to do is set up conditional formatting to change the cell to pink if:

A)the cell contains the "Need"

and

B)The date in the corresponding row from column C is more than 15 days old.