formatiing a cell, e.g. change color to RED, in the event the value is a duplicate. To make it clear, I need to show that if the Serial Number of a product just entered is a duplicate and should change color when a combination of conditions is true. i.e. if a "part number" AND the currently entered "serial number" already exist, the entered serial numbers should change color.
View 3 RepliesI'm looking for a Conditional Formatting formula that will check two columns before highlighting the duplicate rows. I need it to be conditional formatting because I know nothing about writing macros or vba (what-ever that is?). Data is entered into Columns A, B, and C. I need to check both column A and C before it highlights the duplicates, based on those two columns. (The format only unique or duplicate values checks only one column.) I have attached an example, but this is just an example, as I have hundreds of lines to go through on the original. (For this example, Row 2 and Row 7 are the duplicates I need highlighted.)
View 3 Replies View RelatedStart dateEnd Date01/01/201101/01/201102/01/201101/01/201110/01/2011
The above is the result that Im trying to get and I just cant work it out,
Basically,
If B is Blank Format Red
If B is populated and A minus B is less than 5 No format
If B is populated and A minus B is Greater than 5 format Red
-- Im sure its easy but I just cant get it
Is there a way color a cell red if the the text is "Z" or a number between 0.5 and 10
Or if the cell contains a "Z" or a "C" ....
how to adapt the solution so it would work in multiple cells. Since I was told to start a new thread, here it is. I want to format a cell based on its contents, If it has one of three entries, it should be greyed. I then want to use the conditional format across the whole page, but the solution given in the other thread refers to a single cell, how can I do this without that reference?
View 2 Replies View RelatedI'm trying to set up conditional formatting in a spreadsheet where there are 3 possible correct letters that can be entered into a range of cells and if one of these letters is not entered then I would like the cell to be highlighted.
To be more precise, it's an accommodation spreadsheet and in each cell if someone needs a room for that particular night (each night is represented by a separate column) then a letter is used to indicate what type of room they require i.e. "k" is a king room, "s" is a Suite and "t" is a twin. I don't want to force people to have to select from a list which would be the obvious solution to this as it is simply too cumbersome for the amount of data entry we do.
However i want to ensure that if someone accidentally enters anything other than t,k or s then the cell is highlighted. We work in large spreadsheets covering months of dates so to manually have to find any errors is hugely time consuming.
I am trying to do a conditional formatting based on the result in one cell but it needs to add up multiple cells first to determine which conditional format it needs to use. I am not sure whether I need to do a formula conditional format or whether to use cell is greater than with =sum() in it. I have tried both but neither seem to be working correctly. I have attached an example of the spreadsheet and the conditions I am trying. I am using 2010 version.
Basically, the closing amount for Monday in prod 1 (cell c4), needs to display in red if sum of the cumm value for Tuesday (L4), Wed (N4), Thur (P4) & Fri (R4) is greater than that value or display in amber if the sum of the cumm value for Tuesday (L4), Wed (N4), Thur (P4).
I need to complete the same for the whole column for Monday (col C) for each appropriate product. Then the same for column B but this will sum different cells but the principal will be the same.
I have created a spreadsheet that will show me where people are working on what day, etc. however i want to be able to filter by week to create a list of say 2 particuar shifts - in this case "syl ld" and syl n so that the spreadheet would show the people who are working these shifts and I could print out. I have tried Multiple Filters and Advanced search but cannot achieve what I require
View 3 Replies View RelatedI Have 2 columns I want the left column to match the right column if there's any matching it will color yellow using conditional formatting in the left column. I tried left column=right (a:a) but it gives error .
Here the example
Left` Right
508130436508130436
569410115569410116
506141684506141685
507246387507246386
503829286508130436
559789017559789018
558960097558960090
559655269503829286
503790024503790024
559655268559655269
559655270503790024
561993273561993271
503790024561993271
503829286561993271
I need a conditional format that highlights duplicate values in different color. For example 221 and 133 are duplicate values in a range, but I want them to not show only in red. I want duplicate values in the range to show in an individual color.
I have an idea of running a macro that counts all the duplicates, paste them in a separate sheet where only the duplicated values will show, then have a regular conditional format of "Equal to".
How can we find or highlight the duplicate value in two different sheets using conditional formatting.
View 3 Replies View RelatedAs per my attached file : duplicate value.xlsx
How to highlight duplicate value by using Conditional Formatting in office 2010.
Actually i Know how to use this feature in sheet "example" of my attachment.
But i want to highlight inv# 457878268 in cell E9 of sheet "My requirement" if it is exist above in cell E2 to E6.
How do I select certain columns for duplicate values? When I go to conditional formatting>highlight cells rules>duplicate values it checks the whole sheet which is 20 columns by 50000 rows and freezes the PC. I just want to select certain columns for duplicates, like C and AC.
View 9 Replies View RelatedIs there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
I'm desperately trying to work out conditional formatting for different dates.
I have =TODAY() in F3
In the remainder of column F (from F19 to F625) I have either blank cells or varying dates (dating back to 2011 and dates going forward into 2014).
I need to Red/Amber/Green the following:
Green = any dates which are 45 days or more in the future from today's date
Amber = any dates which are between +1 and +45 days in the future from today's date
Red = any dates which are today's date or a date in the past
I can get one or two of the criteria working i.e. Green and Red, but not the Amber. Is one overriding or ignoring another?
how to create VBA code for conditionally formatting a column of dates that have expired. My unique challenge is that I first need to associate the item attached to the date with the expiration date of a particular group. Therefore, I have to first match the Item label with the appropriate group, and then I need to evaluate the expiration dates of both the Group and the Item. If the Item expires before the Group expires, then I need the Item date to be formatted in bold and with a red font.
Attached is a sample spreadsheet.
Thoughts about how to accomplish this with VBA? By the way, VBA is the necessary approach due to the fact that the data is updated several times per day, and all existing data is erased prior to uploading new data.
I have a column of information. The first cell has the record number. Then there are criteria the record must meet. If any of the cells have N for nonCompliant, i would like for the record number to change colors. If it were only one citeria, i think i know how to do that, but because there are as many as 10 I'm not sure how to make it change colors for all ten unless i do it one cell at a time.
Also there are several thousand columns on different worksheets, how do i apply the conditions to each column without doing it manually?
I need to assign 3 critera for Red and similarly for Green 3 Criteria ..
For ex: Red:"3yr"≤40%
I am trying to figure out how to set up a spreadsheet, that comprises a list of open-entries, wherein the user will insert their information and a % out of 100 assigned to them; the entire form, in the end, cannot go beyond 100%, so each entry has to be done correctly...
BASICALLY my problem is that I am trying to set up the " Total %" cell, which is already assigned the formula "=SUM(D5:D16)" (D5 through D16 being the cells in which %'s are assigned) so that it will auto-populate with the appropriate color/error if the %'s are either insufficient (short of 100%) or exceeding (above 100%). What I had been trying to set up (and have successfully set up in other spreadsheets, however under slightly different circumstances) is a Conditional Format in which the first (with the color Red for "exceeding") was " =If(SUM(D5:D16)>100,SUM(D5:D16),SUM(D5:D16))
" So regardless of exceeding, exactly met, or insufficient totals, it would post the total entered, BUT assigning the color red, so that IF it did, in fact, total higher than 100, it would appear red. A similar formula would be applied to Green (reflecting "on target") in which the formula would be " =If(SUM(D5:D16)=100,SUM(D5:D16),SUM(D5:D16))"
Now the problem is: this is not working for the SUM of the cells. In my previous experience with Conditional Formatting and color assigning results, it had been with the AVERAGE of two columns of cells.(worked without a hitch.)
I recorded some code and cleaned it up to apply a conditional format
Sub condi_format_I()
Dim wbBook As Workbook
Dim ws As Worksheet
Dim rng As Range
Set wbBook = ThisWorkbook
Set ws = wbBook.Worksheets("Summary")
Set rng = ws.Range("C4:P52")
With rng
.FormatConditions.Delete
.FormatConditions.Add xlExpression, Formula1:="=C4<0"
.FormatConditions(1).Interior.ColorIndex = 45
End With
End Sub
I would like to apply a custom color index to all cells in range C4:P52 if the value is less than zero.
But when I execute the macro, many cells receive the custom format that do notr match the custom format. Other cells that should receive the custom format do not
I have a worksheet titled "detail" containing 3 cells which automatically and independently change colour subject to their proximity to various dates. On a new worksheet titled "overview" in the same workbook I would like to have one cell which changes colour if any of the 3 cells in the other worksheet are triggered.
View 2 Replies View RelatedI have a conditional format that does not seem to be working for me. Cell B2 has a drop down optionSelect, No, Yes); Cell B3 is supposed to be conditionally formatted to return the following results if the criteria is met:
If B2 is equal to No or Yes then colour should become Yellow
If B3 is >0 then colour should become Blue
The problem is when B3 is greater than 0 it does not change the cell colour to Blue.
B3 Conditional Format #1 is =AND(ISTEXT(B2),B2<>"Select")
turn background to yellow
B3 Conditional Format #2 is =AND(ISNUMBER(B3),B3<>0)
turn background to blue
See attached for spreadsheet with conditional formats
I am drawing a blank. I want to use conditional formatting in cell b2 that will outline the cell(I know how to do that) based on a formula. The formula I need would determine if cell a2 is 26,27, or 28. Or function just returns true/false. I would rather not use 2 formulas.
View 2 Replies View RelatedI would like to have excel check if eight of ten of cells in a row are above a certain criteria, then if they are format the tenth cell to be red...so excel checks the ten cells above and if eight of them are above a certain number then that cell shades red.
View 6 Replies View RelatedThe code segment from "Get Around Excels 3 Criteria Limit in Conditional Formatting" works perfectly if I type the variable directly into the cell, but not in a range of cells. That is to say, if I copy the source cell to a range of cells I receive a "Run-time error 13: Type mismatch" error". Is there a way to get around this? This is the code segment I've tried in the worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("b2:q53")) Is Nothing Then
Select Case Target
Case "IBBCH", "ibbch"
icolor = 36 'light yellow
Case "OBBCH", "obbch"
icolor = 34 'light turqoise
Case "OBBRDG", "obbrdg"
icolor = 35 'light green
Case "LNCH", "lnch".........................
I need a formula that will tell me what Tracking # in the spreadsheet has multiple dispositions and what those disposition are. I need the formula to use column and cell coordinates and not using word searches.
example
Tracking # Req.# Disposition
1490069 045 snakes
1015907 047 candy bars
1488026 044 chickens
1015907 047 sand paper
1015907 001 fruitcups
1490129 046 cupcakes
1484817 043 Cats and dogs
Column b in sample is conditionally formatted based on it's values. I want to also apply that same formatting to the person's name in the chart in D2:I9. For example, Jeff is in bottom 50% so cell B2 is shaded red with red text. I would like to apply that same red shade and red text to all the cells in my chart that say Jeff. Also, as example, all of the cells in my chart that say Kelsey would be formatted with green shade/green text and so on...
View 1 Replies View RelatedI need a macro that will highlight a row based on a cell criteria. I have used conditional formatting but there is a lot to format and has dramatically slowed the sheet down. However, I know nothing about macros/vbas.
E.g. If it states JI sent then I want the whole row to be highlighted light green.
App form needed = peach
Waiting list = light blue
Cancelled = red font strikethrough
DNA = purple font strikethrough.
Also - it could well be that in the row it has App form received, JI Sent and DNA all in 1 go. So ideally I would want that row to be highlighted light green with purple font strikethrough.
Booking Status
JI Status
Date JI Sent
Attendance
Cancellation date
Chargable / non chargable Cancellations
Chargeable / non chargeable DNA
Date taken
Time taken
Event Title
Start Date
Time
App form received
JI Sent
[Code] ......
I have sales rep data with column headings as Sales Rep, Sales Date, Sales Item Code and Customer ID. There are only 2 sales reps in the data, I'll call them Rep A and Rep B. I need to extract rows where Rep A and Rep B have made sales on the same day to the same customer (it doesn't matter what type of sales). I don't want to extract sales by only one rep, it must be both reps on the same day to the same customer. I have tried a pivot table but it identifies all sales. Can this be done?
View 2 Replies View RelatedI have a gantt chart that was built in excel. I need to shade cells in the timeline based on persons title under each project.
I can provide a snip of what the layout looks like for a better visual. I want the PM's Green, Supers Yellow and Persons Reporting Orange.
To get the green bar shown is using formula: =SEARCH("Project Management",$E17) , The issue with this is it fails to consider the start finish dates. For this particular project it works but when the formatting is "dragged down" the issue arises.