# Multiple Conditional Format Conditions Changed Based On Another Cells Value?

Jun 12, 2009

A1:A6 have numeric values 1 to 6.

I want the conditional format values to change Based on the value in Cell B2.

If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text.

IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text.

I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.

## Conditional Format Based On Multiple Criteria

Feb 16, 2010

I have a spreadsheet that requires me to manually hunt down records that match based on several different criteria. this is very time consuming and inefficient. i would like to find a way to highlight certain records that match based on several different fields. i have posted an example spreadsheet that also has a more detailed description of what i am trying to do; which is located in the second sheet.

## Delete Cells Based On Multiple Conditions?

Jul 16, 2013

I'm trying to remove cells that contain certain suffixs. Here is the code I'm trying to use:

Code:
For i = 7 To 2000
If Cells(i, 7) = "IV," Or Cells(i, 7) = "Jr," Or Cells(i, 7) = "III,"
Then Cells(i, 7).DELETE Shift:=xlToLeftEnd IfNext i

but it does absolutely nothing. I've used this for similar macros in the past and it worked just fine.

## Excel 2010 :: Conditional Format Based On Multiple Criteria

Apr 25, 2014

I would like to shade my cell yellow if the priority equals A or A-NEW and ACV=o.

In the data below, items 4 and 13 would be shaded yellow.

I am using Excel 2010.

Priority A, B, C

Consumer UPC

Item Description

ACV Weighted Distribution

[Code] .......

## Test X Conditions In Conditional Formatting: Highlight Cells Is Two Conditions Are Met

Oct 17, 2007

I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND(\$J2=0,\$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard

## Color Cells Based On Multiple Conditions Of Different Cells

Jan 15, 2008

I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it!

Condition and Results required would be:

IF column C = “S” than on Column B = color cell light blue with white border
IF column C = “P” than on Column B = color cell Green with white border
IF column C = “A” than on Column B = color cell Yellow with white border
IF column C = “L” than on Column B = color cell Red with white border
IF column C = “C” than on Column B = color cell Dark Blue with white border
IF column C = “ ” than on Column B = color cell white
IF column C = “V” and has white background than on Column B = color cell white
IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey

I don’t know if the last two are feasible.

## Conditional Format Formula Based On Two Cells.

Apr 9, 2009

I want to create a conditional format formula which is based on values in 2 cells.

Example:

Cell B1 = 100
Cell C1 = 50

I want a formule for cell A1 which turns red for exmaple when B1 is higher then 85 and Cell C1 is lower then 85. Only in this case the conditional format should work.

## How To Conditional Format Based On Adjacent Cells

Oct 18, 2013

Picture1.jpg

How do I create a CF if a cell is blank (in my case represent no sales) i want to have it yellow (ex. C2)

But if it has two consecutive blank cell i want to CF with Red. (Ex. F3)

But i also need to exclude blank cells when we we have no previos sales of that item (Ex.B3).

## Format Cells Based On Conditional Count

Feb 15, 2010

I have a drawing register that contains drawing reference numbers in column D. When a new revision of a drawing is issued it is added to the bottom of the list. What I would like to do is any previous revisions to be greyed out and struck through.

The conditional formatting
= COUNTIF(\$D\$1:\$D\$100,D1)>1
works well but strike through all duplicates, I need the most up to date left without the format until it is superceeded

## Count Unique Occurrences Based On Multiple Conditions In Another Cells

Sep 19, 2013

I am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.

I've attached an example sheet : Example email report.xlsx

The result I need is: The number of unique values in column E, that also have NULL in column G and NULL in column I.

So, in the example, the result would be 7.

I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.

## Fill Cells With Some Specific Texts Based On Multiple Conditions

Jun 18, 2014

I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.

Code to fill these blank cells automatically with some specific texts based on the attached conditions.

I have attached the workbook for more details. FillCellsTest.xlsmâ€Ž

## Conditional Format Whole Rows With More Than 3 Conditions

May 14, 2012

I am needing to format a spreadsheet using 2003 which only allows 3 conditional formats, but I have 4 conditions.

I need to highlight the row if column W has a

G - green (colorindex = 35)
R - red (colorindex = 3)
Y - yellow (colorindex = 36)
O - orange (colorindex = 44)

how I can do this?

## More Than Three Colour Conditions In Conditional Format

Apr 15, 2009

We have 6 words we need to add to a sheet, each cell needs to be a different colour on the basis of the word.

I was hoping to use CF as the sheet needs to be locked against people editng cells and the like.

My question is, could a conditional format say if "service" colour blue, if "parc" colour green.

If this is possible this would allow me to use 6 different colours.

## Excel 2007 :: Copying Conditional Format To Multiple Cells

Nov 5, 2013

I'm a 2007 user. I am trying to conditional format a range of F1:J10, where F1 will be conditionally formatted relationally to A1. G1 is relational to B1. H2 relational to C2, etc., all the way down to J10 relational to E10. My conditional format formula in cell F1 is >A1*2 (will format F1 as orange). I want to be able to copy the conditional formatting over so the formula in J10 for example would be: >E10*2 (will format it orange).

I omit the "\$" in the conditional format formula in an attempt to prevent it from being absolute. But every time I copy the conditional formatting over to the other cells, the formula within those cells remains identical to the formula in the original cell.

## Conditional Format X Number Of Cells Based On A Single Cell?

May 4, 2014

I am looking for a method to conditionally format a variable number of cells below a single cell into which data is entered.

For example ABC = 6, DEF = 12. Therefore, if I enter ABC into D1 then D1:D6 should be conditionally formatted.

## Conditional Format Cells In Jan - Dec Columns Based On Next Column Data?

Jan 29, 2014

Wondering if it is possible to conditional format cells in "Jan"-"Dec" columns based on "Next" column data?

What I need is (lets say fill colour to differ) in one cell per row, based on latest input and "next" data.

Month Avg
Date
Jan
Feb
Mar
Apr
May
Jun
Jul

[code]......

So for example:

row2: May is latest input so need fill in cell +2("next" column data) from may. In this case July

row3: Feb is latest imput and "next" also 2. Result should be Apr

row8:May is latest input, "next" is 6. Res should be Nov.

Finally, only one cell per row to be highlighted so need to overwrite colour fill when new data input in a cell already colour filled.

## Conditional Format Color Of Cell Based On Values In Range Of Cells?

Oct 24, 2013

I would like to format the color of cell A1 on Sheet 1 based on true or false values from cell range A1:A10 on sheet 2. For instance:

1. If all cells on sheet 2 in range A1:A10 were false then cell A1 on sheet 1 would be red.

2. If some cells on sheet 2 in the range A1:A10 were false and some were true then cell A1 on sheet 1 would be yellow.

3. If all cells on sheet 2 in range A1:A10 were true then cell A1 on sheet 1 would be green.

## Count Number Of Times Conditional Format Conditions Met?

Apr 22, 2014

I have used Formula to ID cells containing a formula to flag cells red with conditional formatting in a list that do not have formula.

I need a single cell to change colour if any of the cells in the list do not have a formula. e.g. the subtotal in the top row could go red so the user can scroll down to find individual red cells.

Is there a way to count the red cells without formula in the list - Conditionally formatted cells are hard to count. I don't want to use a vba script to do it as the user probably won't run it and auto run slow calculations down.

Is there an array formula that can return true or false if not(CellHasFormula) is true anywhere in the list?

countif(AH1:AH2976,CellHasFormula) doesn't work.

## VBA Conditional Format Range Of Cells Based Upon Criteria In One Cell / Repeat For All Rows

Apr 3, 2014

Code is trying to

1. Where there is data in column B
2. Goto first row of data
2. If column J = "Closed"
3. then select Cells from column B to J in that row and colour these cells in red
4. If column J= "Open"
5. the select Celss from column B to J in that row and colour these cells in grey
4. Repeat for next row until you get to cell in column B where there is no data.

Recieve run time error 1004: Method 'RAnge of object_GLobal failed

===============================================================
Sub Colourclosed()
Sheets("Risks").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LastRow
If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select
Selection.Interior.ColorIndex = 3

[code].....

Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells

## Protect Certain Locked Cells From Editing And Allow Certain Unlocked Cells To Be Changed On Multiple Worksheets?

Jan 31, 2014

1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.

2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)

3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .

## 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.

## Conditional Formating: Highlight Cells Based On Multiple Stipulations

Sep 14, 2009

I'm trying to get it so that a cell becomes automatically highlighted if 2 other cells conditions are met. For example, I want cell A5 to become highlighted if BOTH cell B1=Loan 1 AND cell B5=Yes. Here is a mockup of what I'm talking about incase it makes it easier to understand. I'm using Excel 2007.

## Conditional Formatting Based On Several Conditions

Oct 11, 2013

I am trying to do some conditional formatting to highlight rows based on several conditions, I don't know if this is possible, but these are the criteria:

AAPL
tt
10/1/2013 11:14:15

GOOG
ll
10/1/2013 11:14:20

[code].....

I want to highlight a row if:

- the difference in time value is < 20 seconds

- the first symbol column is the same

- at least one, but not all of the rows meeting the above criteria contains a value in the second column from a given list (in this example, say tt is on this list)

So if done properly the logic would highlight the first and third rows, but would highlight none if none of the values in column two match my list.

I think the test criteria would go something like this, however I know that there are problems with my row references.

=and(abs(\$C2-\$C3:\$C65536)<0.0003,\$A2=\$A3,match(\$A2,Sheet1!A:A,0)>0)

## Format A Cell To Accept Multiple Conditions

Jun 12, 2009

if I want 2 conditions satisfied in order for a value to be returned, would I need to set up/name tables, set ranges, etc? e.g,

If A1 contains baby, AND B1 contains Carly, then C1=carly.jpg
If A1 contains girl, AND B1 contains Carly, then C1=carlyg.jpg
If A1 contains baby, AND B1 contains Billy, then C1=billy.jpg
If A1 contains summer, AND B1 contains Wally, then C1=winter.jpg
etc.

Roughly 40 different conditions will be needed. What formula would I use and how should I name and set up my table?

## Conditional Formatting More Than 3 Conditions Based On Dates

Feb 8, 2009

I want to create a chart than logs the progress of a project at work. Column 1 will have a series of target dates for certain tasks and column 2 will be filled in with the completed dates. I want automatically colour a cell (RC) based on the date given in the cell to the left (RC[-1]) giving a traffic light style warning of an upcoming target date and colour coding the cells when a task is completed on time (or not!). I can work out what the conditions are by creating 2 sets of 3 conditions (1 set if cell (RC) is empty and 1 set if cell (RC) has a date or "N/A" in it).

My conditions are:

1. Turn the cell background colour red (action overdue) if:

Formula Is:=IF(AND(RC[-1]"",RC="",RC[-1]

2. Turn the cell background colour orange (action required) if:

Formula Is: =IF(AND(RC[-1]"",RC="",RC[-1]

## Format Cells On Conditions

Jul 12, 2007

I have a Macro that when run checks for the lowest value in a line & highlights that line. It works just as it should, infact it works better than I had planded. When I update a cell value and move to the next cell it changes the highlight.

Option Explicit

Sub SELECT_LOW_PRICE()
Range("a4:i126").Select
Selection.FormatConditions.Delete
Formula1:="=and(a4=min(\$a4:\$i4),not(isblank(a4)))"
Selection.FormatConditions(1).Interior.ColorIndex = 6
Cells(1, 1) = "Updated " & Now
Cells(1, 1).Select
End Sub

## Conditional Formatting Based On Another Cell Conditional Format

Mar 20, 2013

Is 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???

## Automatically Calculate 2 Cells Based On Cell Changed

Apr 25, 2009

I have some rather complex formulas that when a value is placed in one of any three cells it solves for the other two, taking values from other places on a worksheet (including the cell itself).

Scenario: Cell F10, G10, and H10 all have formulas in them. If a value is placed in F10 it solves for G10 and H10. If a value is place in G10 it solves for F10 and H10. If a value is placed in H10, it solves for F10 and G10.

I really do not think the actual formulas matters at this time...

Has anyone done this before, using VBA code to place the formulas in cells F10 G10 and H10? Look for a working example of this if possible.

## 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â€Ž

## Conditional Formatting - More Than 3 Conditions - Result Based On Another Cell

Aug 3, 2009

I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem:

Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.

i.e. (in reference to attachment - sheet DATA)
IF E6 = Holiday, then D6 = Red
IF E6 = Half Day Holiday, then D6 = Red
IF E6 = Sick, then D6 = Blue
IF E6 = Sick Half Day, then D6 = Blue
IF E6 = Bank Holiday, then D6 = Green
IF E6 = Compassionate Leave, then D6 = Pink
IF E6 = Unpaid Leave then, D6 = Yellow
IF E6 = Unpaid Leave Half Day then, D6 = Yellow

However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).