In column C, I have a list of phone numbers of some 10,000 items. In column F I have 50 telephone numbers. I need a formula or a conditional formatting solution that will allow me to mark/indicate/color those numbers in column C that are in column F.
I would prefer to do this without a VBA solution.
I have conditionally formatted a column of staff names where duplicates are highlighted - this is fine but there are some duplicates that I do not want to include - these all have the same criteria in that they include the suffix (v)
Is there an easy way of doing this?
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
I have five fields that have a conditional format applied to them: (see attached).
Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.
Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
I have a range which has conditional formats based on other cells. I want to copy this range into another sheet and retain the current formats as fixed formats without copying the conditional formulae. ideally in VBA.
View 5 Replies View RelatedIs it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
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.
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 am trying to improve my expense report template and need to check on the load if expense report number has been loaded correctly as well as if this report has been previously loaded. Expense report number format looks like this: AAA-BBBBBB-CC
Where:
AAA – Employee ID #
BBBBBB – End of the week date
CC – Weekly expense report number
For instance, 023-122008-01 means: Employee number 023, week ending date 12/20/2008, weekly expense report number 01. I would like to prevent/give warning of loading incorrect expense report number format, check for possible duplicates, and check if trying to load expense report belongs to the right person (by simply matching previously loaded in different cell of the same sheet employee ID and first three digits of just loaded expense report number. I think I know how to do all of these separate, but have no idea how to combine all three checks for one cell.
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?
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 Replies View RelatedI 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 RelatedI am trying to teach myself something new, say I have a list of numbers in two rows and I want to count the numbers and have it tell me if there are duplicates and highlight the duplicates. I got the part about getting Excel to tell me if there are duplicates but I can not seem to figure out the conditional formating part. I uploaded a sheet.
View 3 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?
I'm trying to conditionally format rows of data based on duplicates in the first column, then filter the results. I have a table of data with mutiple variables assigned to different "headings" that looks a little something like this:
A
B
C
D
[Code]....
[selects the table, then GoTo Special selects the blanks, fills in the blanks with the cell above, then Copy and Pastes As Values the entire table again to fill everything in]
I’m stuck on how to input a formula into the conditional formatting window that will:
Format the text to white in columns A, B, and E based on there being a duplicate above that row in Column A only. i.e. conditionally format the values with a * below:
A
B
C
D
E
[Code]....
If I use the conditional formula I found: =A1=A2, then cell E3 gets made white text when I don’t want it to, hence the “referencing column A” part of the question (Column A is always a unique ID number whereas Column E can have a duplicate in the row above). [Edit: Why can't I type Enter or put a line break here... I'll try re-edit at home...] When I go to filter on Column C for YYY again, the conditional formatting needs to realise to un-white the text, which is my next headache because it means conditional formatting that acts relative to hidden rows as a result of a filter... Oh and did I mention the client wants this done in 2003? This is an afterthought though – as I can force them to use 2010 if need be
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.
View 2 Replies View Relatedhow to view changes on my spreadsheet.
I have a tab called SAP DUMP where we copy our raw data to, from here we manually copy and paste into another sheet called ORDER TRACKING SHEET. When we do a fresh dump (overwrite) of raw data into the SAP DUMP tap we need to see if the there is a new entry which needs to be copied across to the ORDER TRACKING SHEET. I've got a vlookup to show this. The one I can't work out is that on the ORDER TRACKING SHEET I also need to see if there are any entries are now no longer on the SAP DUMP tab. This is where I thought a condition format might work, color the line red if it's no longer on the SAP DUMP tap. Can't make it work.....
I have a workbook with 12 worksheets corresponding to months. Each is formatted in A1 with the month (Date, Jan-01) and all rows below, starting at A5, are the days of the month (Custom, ddd d). All cells are linked so they change accordingly with A1.
I'm trying to apply conditional formatting so that all Fridays will have light grey shading across the row. I can get it to shade every 7th row using the MOD & ROW functions, but the shaded row changes with subsequent months and years (not necessarily Fridays). Can I get it to recognize every Friday through the year.
Hi1Tasks23-Apr-09Inercona.Trial1
b.Trial2
Tasks23-Apr-09Enercona.Trial1b.Trial2
Disregard the First Table. I cant remove it. refer to the Second Table
This table is a Task Tracker. The Start Button Adds the Current Date in Enercon's Row C1 and the row below (C2) will automaticaly filled with color using Conditional Formating. My Question is how can I code a conditional format for Trail1 Rows. For example when Trail1 Cell is active then you click the start Button instead of Coloring D2 Cell it should Color D3 Cell.
Here is the Code in Start Button
Sub cmdStart_Click()
Dim i As Integer
Results = MsgBox("Are you working on " & ActiveCell.Value & " today ?", vbOKCancel, "")
If Results = vbOK Then
With Sheets("Sheet1")
i = .Cells(2, Columns.Count).End(xlToLeft).Column + 1
.Cells(2, i).Value = Now()
i = i + 1
End With
Else
Range("A1").Select
End If
End Sub
Regards,
Kevin
I would like to conditionally format a column of cells such that it formats when a cell's text equals to that in another column.
Example, in column A I have
1 Cat
2 Dog
3 Mouse
...
In B I have
1 Chicken
2 Mouse
3 Monkey
...
58 Cat
Based on the B column I would like to highlight A3 Mouse and A1 Cat.
Additional complexity, In B I have
1 Chicken Mouse Dog
2 Rat Monkey Fish
3 ...
Given that this is the format I have the text in how would I search column A and conditionally format A2 and A3.
I am having trouble with a conditional format of a date. I need it to turn the date red if it is 35 days older than today.
View 3 Replies View RelatedI have a conditional format that I wanted to add another argument to, but although the statement is true, conditional format will not toggle.
My original argument was:
Maybe I'm getting a blank, maybe the answer is so easy, but lately I can´t solve this: How do I use conditional format to change cell color if cell value (date) is not workday?
View 4 Replies View RelatedIn column B I could have blank, text or text containing the word "track" or "Track". In column C I could have Blank, "yes" or "Yes" or "no" or "No"
Only if B text contains "track" or "Track" and/or C contains "yes" or "Yes" I want to conditionally format B.
This does not work:-
=OR(FIND("track",B1,1),FIND("Track",B1,1),C1="yes",C1="Yes")
I have a table where Sales are in Column B and a % calculation is in Column E. I want to highlight the cells in green that are over 40% and that also meet the criteria where the sale in that row is above $100.
In conditional formatting I tried to use =and(b4>100,e4>40%) but it did not do anything. I am also okay with using a nested if statement.
is there a way to have a cell display a certain text if it is empty?
i.e. i would like all the blank rows in a section of my spreadsheet to have a grey fill and display the text "Please input your description here" if the cell is blank.