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. . .
View 4 RepliesI 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.
View 12 Replies View RelatedMy 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.
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
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.
View 2 Replies View RelatedI 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?
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.
View 2 Replies View RelatedConditional formatting duplicates across multiple sheets. I have been using the formula
View 5 Replies View RelatedI 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?
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?
Have 2 conditional formattings going on in a database query and need to apply two more conditions but only have 1 conditional formatting left. I am trying to highlight everything in a row that contains either "HCAT" or "HCAL" in column D. So far I have this,
=$D2=OR(HCAT,HCAL)
but its not highlighting anything, any clues?
I simply need to have column A be red if value is < Column B value and green if it is >. The problem is that if the value in column A is .00000004 and column B is .00000005, it turns red. Example is the first three numbers below the headers....they each are 0%, yet the value on the left turns red because it is .0000005 more than the value on the right. I just want the colors to reflect the numbers which show up as below. Is there a way to do this?
2011 % of PDD
2011 % of Total $
0%
0%
0%
0%
0%
0%
[code].....
1. I have a range of random numbers within a column, i.e. A1:A9
2. A specific number will appear in a cell at the end of that column, i.e. A11
3. I need to see via conditional formatting which of the above cells add up to that number.
I am aware that given the numbers in A1:A9, it is possible to have more than one combination of numbers that add up to A11. I would need to see those combinations as well identified using Conditional formatting.
I am referencing a cell in order to change the background color of a row of cells if the number in column B is = to the number in a specific cell. The formula used in the conditional formatting is =$B73=$B$10. The reference cell $B$10 contains the following formula =INT(MAX('Assumptions & Inputs'! F8:F9))
The conditional formatting worked fine when I used that formula. I had to change the formula in cell $B$10 so that it would meet a new criteria. When I did that the conditional formatting would not change the color of the row. The new formula that I put in the reference cell $B$10 is
='Assumptions & Inputs'!$D$8+MAX('Assumptions & Inputs'!$F$8-'Assumptions & Inputs'!$D$8,'Assumptions & Inputs'!$F$9-'Assumptions & Inputs'!$D$9)
I have a challenge with conditional formatting. I have products A-K with sales current year and last year and want to have year on year growth. I want to have one column where the cell formatting changes based on data validation.
Let's say product A sold 60 items CY and 50 LY meaning YoY variance is +10 items or +20%. Hence, if the relevant cell says "%" then the YoY figure should say "+20%" (formatted as a percentage) and if "VALUES" then YoY should be 10 (formatted as numbers/absolute values).
Remember also that YoY growth can be +100% (or more), 0% and -100%.
I have a list that, how do i create a border that data contains formula (formula not display/blank)...
i want to create dynamic border with condition if data display/showing must be border shown too...
for this, i want to line "TOTAL" in column AD expands up, based on data display/showing...
note : in cell AF2 as parameter/controller to show data by month (type 2 instead February or 3 --March etc...)
I am trying to use conditional formatting for a cell; It is a cell using Validation; you can choose "Yes" or "No" from a list If the value is "Yes" the cell should be green, regardless of everything else But if the value is "No" it should turn red, but only if a choice in another cell is equal to "X". Otherwise it should be left without special formatting.
View 2 Replies View RelatedI am having a very difficult time trying to figure out a conditional formatting formula for something that seems very simple. There are 3 conditions I would like to apply to cell B2: If the value of I2 is less than or equal to 24, but greater than or equal to 21, shade B2 red. If the value of I2 is less than or equal to 20, but greater than or equal to 12, shade B2 orange. If the value of I2 is less than or equal to 11, but greater than or equal to 8, shade B2 yellow. I am sure I am missing something quite simple here.
View 5 Replies View RelatedI am having an issue, because aparently workday formula does not work in conditional formattings.
I have the two formulas below to use in conditional formattings, but I can't figure out how to re-write them so that Excel will allow me.
I am trying to set some conditional formatting, but for some reason on of the conditions is not working and I was wondering if anybody know how -
View 3 Replies View RelatedI am trying to create a formula in the Conditional Formatting wizard.
I would like the formula to format B2:F2 in a particular way if a count of the number of values in this range equals 5 AND if all values are less than or equal to 25.
I have tried with following formula without success:
=IF(AND(COUNT(B2:F2)=5),COUNTIF(B2:F2,”<=25”)=5),””))
This is the most simplest conditional formatting which is working except on some cells. I want to compare two columns cells ex : if A=B , then format A as green.
Its working, the "formula" and "cell value contains" everything except for few cells. Cell B has formula's in it related to other cells. When i enter values in other cells, Cell B computes but A refuses to format "green".
If I just enter same value[ex 1, 1]in both the cells sans formula ,it highlights A green . I want to know , if the formula in cell B is causing the errors in formatting and if it is
I have a grading system for university students, my spreadsheet looks like this:
Student Name Academic Calendar GPA Fall GPA Winter GPA Spring GPA Summer
John Quarter 3.00 2.56 2.89 3.6
Jane Semester 3.1 n/a 3.21 3.7
I think I will need to do some conditional formatting to make it easy to fill the student GPAs .
I was thinking how to enter a formula to have the GPA winter appear as shaded when I enter "semester" in the Academic Calendar column.
I have a sheet which contains conditional formatting formulas.How i can Lock or protect the forumluas to view others.
View 2 Replies View RelatedMy attached spreadsheet is for tracking when certain tasks were completed on each case and figuring the number of days that it takes to complete these tasks.
The task I am needing help in formatting is the Corrections Received field (column E). We need to track the cases that do not have corrections received within 7 days of the changes being requested (Out for Corrections, column D). The issue is that there would not usually be a date entered in the Corrections Received column when we need to look at those cases...because they would not be received yet. In order to not get an extreme default value (like the -40181 or 40922), I entered the =Now() function in column E (except for first case as an example of what this would look like otherwise).
What I was hoping to be able to do is use conditional formatting to look for that Now() formula. If the formula is present, the cell would be highlighted. If a date were entered, even if it was today's date, the fill on that cell would be removed. That way we could tell when the displayed date is a true date and when it is a placeholder until we receive the information.
Currently, it either highlights no matter what the cell includes or it doesn't highlight anything...
My VBA code should post the formula "=IF($B6=""Store URL"",TRUE,FALSE)" into the conditional formatting formula box but everytime it pastes something different, such as "=IF($B11="Store URL",TRUE,FALSE)"
The full code is:
Sheet1.Select
Set ConditionalFormattingRange = ActiveSheet.Range("B6:F505")
ConditionalFormattingRange.FormatConditions.delete
[Code].....
(1) I have conditional formatting working to format cells that contain a formula.
(2) I need to also separately conditional format cells which link to a formula in an external worksheet.
I don't wish to use VBA and would like to use defined name range method outlined below.
To do (1) above:
Under define name range, created "cellhasformula", the formula used in the field refers to was: =get.cell(48,indirect("rc",false))
In conditional formatting using: format values where this formula is true. =cellhasformula (works great).
These formulas in their current form, do not work in defining name range:
=InStr(1, c.Formula, ".xls") > 0
or eg =InStr(1,rCell.Formula,"[")>0
exact formula to use in define name range to return a true value if a formula is linked to an external worksheet?
I have a cell that has a conditional formatting of Cell Value< 90 the cell value is based off a formula which is
=LEFT(E48,LEN(E48)-1)
which when it comes out to me less then 90 I need it to color red. But it does not is there something that I need in the conditional formatting to tell that its a number?
I have two columns:
Column "D" is a date field
Column "E" is a number field
I would like to Conditional Format the Dates in Column "D" to have a color fill:
If the value in column "E" is >99 and the Date in Column "D" is over 42 days before TODAY
=AND($E$3>99, $D$3
For some reason the end of teh formula will not display:
Is it possible to have the conditional format auto adjust the cell value range (similar to how the drop and drag with the autocomplete cell values turned on works)?
For example:
If I conditional format: A1 - does not equal A2 then change colour to blue. Then use the Format Paint to copy the A1 conditional format to B1. Is there any way that it can auto adjust the cell reference to change to - does not equal B2 (the next cell down and so on) rather than having to manually go to each cell and adjust the cell reference?
Ultimately I want the following to happen:
A1 - does not equal A2 (colour blue)
B1 - does not equal B2 (colour blue)
C1 - does not equal C2 (colour blue) etc