I 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
My Goal: If the value of G8 (which is the result of a count formula) matches the value of G10, then apply formatting to G10 through H11.
View 3 Replies View RelatedColumn 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 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.
I need to be able to get a macro to do this. I recorded the macro. The conditional worked, but when I cleared it and then ran the macro, it highlighted all of the wrong stuff. Here is what I want to do: In column(AJ) I have cities with Prefixes or not In another column (AV) I have Criteria1 and in yet another, I have criteria2 (CB)
City
(AJ) Criteria1 Criteria2
SCRNRTH_SCRM1 FALSETRUE should fail test
A9_SCRNRTH_SCRM1FALSETRUE
A9_SCRNRTH_SCRM1FALSETRUE
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
SCRNRTH_SCRM1 TRUETRUE should fail test
A9_SCRNRTH_SCRM1FALSETRUE
SCRNRTH_SCRM1 FALSEFALSE
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
A9_SCRNRTH_SCRM1TRUETRUE should fail test
A9_SCRNRTH_SCRM1FALSETRUE....................
Excel 2002
I have a simple spreadsheet and am trying to get some Conditional Formatting to work.
Cell1 = Name
Cell2 = 1
Cell3 = 2
Cell4 = 3
If cells 2, 3, and 4 all match I want the background in cell 1 to change to green.
I've tried (Formula Is =B1=C1=D1 green) with no luck.
Is 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.
Attached is the example : Example.xlsx
Basically, would like to have the following conditional formatting:
There is a fruit listed and the are person 1 and person 2. If the fruit under Person 1 or Person 2 matches the fruit under Fruit, the cell under either person 1 or person 2 highlights green; if it does not match, it highlights red. If the fruit has not been listed under fruit, such as in cell A3, cells B3 and C3 are not highlighted.
I have an "actual build" worksheet, which allows the user to select which materials were used, material properties (e.g. material grade etc.), including its corresponding length. The user can enter all these in manually, or select the material name from a pre-defined list (Reference worksheet contains this information), and then the associated properties are automatic lookups from the Reference worksheet.
So all this is fine / completed, and the LOOKUP returns its theoretical length from the reference table. However, its actual material lengths will almost never match the theoretical length, so what I want to happen is for a conditional format to highlight the cell, to notify the user that they need to enter a manual value. The auto-looked up length is still useful a proposed build on the worksheet, but having it highlighted to ensure it is manually entered later is the objective.
The other thing is it can't just check if it's a formula, it has to check if it is a formula with a MATCH/LOOKUP function. E.g. it needs to be unhighlighted if someone has a basic formula, ie = 2*0.42 if there two of the item etc..
Hence, I was thinking of having a conditional formula that "format only cells that contain" and then format only cells with "specific text" and "containing" and "MATCH(" text function, however this doesn't seem to work.
Length cell formula:
=OFFSET('Reference- Materials'!$N$5, MATCH($BO32, Materials_Item_Number, 0), 0) * $AH32
The Problem: There is data in a step chart from left to right. On a separate step chart, Conditional formatting is set to lookup values from the first step chart and highlight non empty cells in the row. The formatting should only format the first non empty cell from left to right in the row. After formatting the first non zero value in the time line the formatting should cease. Note: the conditional formatting value and the value of the cell being formatted are not the same in either value or time but are related. The formatting is to illustrate the relation
Current Formatting formula:
=VLOOKUP($B395,$B$5:$DB$211,K$242,0)
Syntax for Vlookup(Lookup_Value,Table_array,Column_index_num,Range_Lookup)
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?
Start 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
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 RelatedIn a column in which there are repeating numbers is there a formula I can enter into conditional formatting that will highlight a cell when the number doesn't match the value from the value above it?
So in this example the values with an * would be highlighted.
1*
1
1
2*
2
2
2
3*
4*
4
5*
5
I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.
Worksheet (functions!)
This one has a list of numbers formatted as general. (Column G)
Is actually a formula/macro that outputs a number... (didn't know if this mattered?)
I have configured conditional formatting to check valid entries in a dependant list. I used the solution provided by SHG at:
http://www.excelforum.com/excel-prog...ant-lists.html
Now I need to use a macro to activate and deactivate the conditional formatting due to performance issues.
When the formula for data validation includes the indirect funtion I got a compile error and the macro won't run.
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
I 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 RelatedIs 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 Relatedformatiing 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 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 want to highlight a cell if the text displayed from an IF formula is equal to the cell content.
CELLS BK10:BN10 are merged and have entered into them the following text "SELL"
Cells BJ20:BP20 are merged and contain the following formula which currently results in the cell displaying "SELL"
=IF(BM22<=-0.08,"SELL", IF(AND(BM22>-0.08,BM22 < -0.03),"NO INDICATION", IF(BM22>=-0.03,"HOLD")))
Cell BM22 is a percentage calculation of the differences between two different days of volume for this stock and that formula is: =(BH22/BH25)-1 Cell BM22 currently is calculating the result to be -65.65%
When I set up a CLASSIC Conditional Formatting using a formula (="If($BJ$20=""SELL""") to check the if the text in cells BK10 match the text displayed in cell BJ20 - I get no error messages and no formatting?
I am using Mac Office Excel 2011. I feel like I have tried everything including changing the Number selection type of the cells to TEXT. Nothing seems to works.
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
