Using An Array/range In Conditional Formatting

Feb 21, 2007

I have this :

1 7
1 27
5 3
5 7
5 31
5 35
5 39
5 59
9 7
9 11
9 31

I need a formula to use with conditional formatting that "lights up" the corresponding cells. So, in this case cells A7, A27, E3... should "light up".
Is this possible, or should I use VB?

View 9 Replies


Using Array And Conditional Formatting

Apr 16, 2014

I have a challenge, which i have attached a spreadsheet. It's probably easier if you look at the spreadsheet, rather than me trying to explain. I've added some notes for reference.

I've tried to use an IF/vlookup function but I cant seem to make it work.

Attached File : ans.xlsx‎

View 9 Replies View Related

Conditional Formatting To Array?

Aug 21, 2013

I am trying to use conditional formating that will highlight any cell in the range M2:P9 are greater than 0 AND where M1:P1 (text) does NOT equal Q2:Q9 (text).

View 3 Replies View Related

Array Changing And Formatting - Range Of Values

Mar 4, 2013

I have an array that refers to a range of values:

Dim vtimeheader As Variant
vtimeheader = Worksheets("output").Range("e4:xfd4").Value

(1) I now want to delete all values in the array
(2) I want to apply the format "General" to all the cells in the array

View 2 Replies View Related

Conditional Formatting Against A Range

Apr 30, 2009

I have a list of names that belong to a particular group. What I want is to conditional format a sheet so that if a name is entered that does not belong to the group it is highlighted red. Don't really know how to do this though?

View 4 Replies View Related

Conditional Formatting A Range

Apr 13, 2009

I have a spreadsheet that has a table listing employee names in the far left hand column (A1, B1, C1, etc.). The table has a simple border - black line throughout.

I need the entire row for the employee to change to no border if the employee's name is deleted.

View 2 Replies View Related

Conditional Formatting For Range

Jan 8, 2010

Using the following conditional formatting formulas:

=(isblank(p99)) then the formatting of (applies to) C99:O99 will be in red and bold

=(d99="Prod.") then the formatting of C99:P99 will be blue and bold

but the results are not as expected. Is something wrong with these formulas?

View 4 Replies View Related

Conditional Formatting: Compare Range To Another Range

Oct 11, 2006

I was looking to use the VBA conditional formatting script posted on OzGrid and was curious if it would be easy to make some slight modifications.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20 ..........................

I can see that Case is the number range referenced to change the background...However, would it be possible to have it read a cells value as the criteria for the color change? Currently, I would like it to reference values in range I2:M2 and anything matching those cells in range A4:E28 change background to icolor = 30. I'm just not sure what to replace Case with to make it refence cells I2:M2

View 6 Replies View Related

Conditional Formatting Using Named Range

Aug 15, 2014

I have a sheet called Quote Summary and a sheet called AssemblyBoms.

In the Bom sheet, I have a named range for QtyPer and a named range for unit price because these ranges are dynamic.

On the quote summary sheet, I want to format the cell (A9 and eventually A9 to A28 and so on), which contains a formula right now that only references a tag and does some equivalence checking


to have a yellow background if QtyPer>0 but unit price = "" or 0. I know the formulas for this, but it is not formatting anything. Attached workbook below.


Conditional Formula for true:
="AND(qtyper1 > 0, unita1 = "")"

I typed it in without the = or "", but the formatting added those in for me.

I typed a number in the qtyper1 range and left the unita1 range blank to test it and no formatting has occurred.

View 10 Replies View Related

Conditional Formatting With Date Range

Feb 26, 2014

I have a column of dates in column M, and in A1 I have the formula =TODAY() for today's date. I would like to conditionally format all of column M (up to row 198) so that it turns orange if the date in M is before today and after or equal to 3 working days before the date in M.

I have this
But it doesn't appear to be working because it's highlighting dates after todays date.

View 6 Replies View Related

Conditional Formatting: Select Range Only

Mar 31, 2009

Have a spreadsheet using the following conditional format to color highlight every other row: =MOD(ROW(),2)=1. Is there a way to apply this conditional format to all columns A through AR and all rows 1 through 24000 only?

View 3 Replies View Related

Conditional Formatting A Range Of Cells

Nov 26, 2009

I'd like my conditional formatting to format the range of cells red if the range K2:S2>H2. That is when the range K2:S2 are added together, and if their sum is greater than H2, format cells K2:S2 red.

View 5 Replies View Related

Conditional Formatting With A Moving Range

Jan 17, 2010

I have a Conditional Formatting query where in essence I am needing to ‘make invisible’ a range of data by changing the font and background fill to white (and at the same time nullifying the effects of a number of other Conditional Formats). On the surface, this seems an easy thing to achieve, but the problem is, the range I need to apply this CF is a moving range and is dependant on a user changeable date.

Description of worksheet:

The sheet is managing shifts worked over 1 year by 21 people. The top 33 rows are a frozen (and collapsing) display area below which is a matrix of 365 days down and 21 people across. The TODAY button uses a macro to scan the dates and display todays entry directly beneath the freeze line of row 33. The date in M28 can be entered manually and after hitting enter, that dates data is now displayed under the freeze line. Also, using the spin button will increment or decrement the date in M28 moving the entire matrix up or down.

My query..........

What I’m hoping to achieve, is to display ONLY the data selected by the date in M28 (ie that shown under the freeze line, and for all remaining rows to be ‘whited out’, but as the user increments/decrements M28, then the “whiteout” range also needs to increment/decrement. I have whited out a second worksheet “example” as if I had selected 18 Jan (this sheet will not move as there are no buttons, but the data is still there under the whiteout).

I hope this is not TOO unclear, but I would be grateful if anyone could suggest a solution or even tell me if this is not possible – or indeed any other way of achieving the same result ( I had thought of hiding the rows below the freeze line, but the freeze/unfreeze operation would probably be too jerky.

View 9 Replies View Related

Use Conditional Formatting On Range Of Cells?

Jan 23, 2013

What I am trying to do, is apply conditional formatting to a range of cells (for example, A2:J2) based on whether the data in one of the cells (D2) contains "Yes" or "No". I would like the entire range of cells to be one color if D2 contains "Yes", and another color if D2 contains "No". If needed, this can be split into 2 different conditional formatting rules.

View 5 Replies View Related

Conditional Formatting From Given Cell Range

Feb 12, 2013

I have a range of data on sheet1 say A1:A10 with random letters in each cell. On sheet2, I also have random letters and number in column A. What am trying to do is find a formula which if any of the data on sheet1 can be found in say A1 of sheet2 then shade the cell a given colour or if formula in B2 then show True otherwise false if not found. then do the same for the remainder of the cells on sheet2 which am assuming will be to just copy the formula in B1 of sheet2 or the conditional format in A1 whichever comes first.

View 4 Replies View Related

Conditional Formatting Range Of Cells?

May 19, 2014

I am wondering if there is a quicker way to set up conditional formatting for a range of cells. In my projects, I have a range of cells N7:U16, where each row (7-16) would need to have a cell formatted based on a value in the cell adjacent to the range. I.e. the cell in range for row N7:U7 needs to be formatted based on the value in V7, and so on down for for rows 7 thru 16. Said another way, if the cell in the row range between n7:u7 = v7, then the cell in the range is uniquely formatted.

But I want to apply this logic all at once, instead of having to conditional format each row individual. The trick is that the cell with the value that I am comparing to is variable (i.e. V7:V16), but does correspond with the row I am comparing it to.

View 3 Replies View Related

Conditional Formatting For Range Of Cells

May 24, 2007

conditional formatting a range of cells where i need more than 3 conditions, so please see below and can anyone kindly construct some vba code for me to do this (And if you have time to explain how it works so i have a good understanding of this)

Range R6 to R299 contains delivery dates
Range AO6 to AO299 is = to the corresponding cells in Range R but formatted to show the Day

Cell AL1 contains a date which changes on a Thursday to show the following week commencing date.

What i am after is code so that Range AO6 to AO299 changes cell colour to the following criteria

less than AL1 (White)
Is between AL1 & AL1+7 (Blue)
Is between AL1+7 & AL1+14 (Green)
Is between AL1+14 & AL1+21 (Yellow)
Is between AL1+21 & AL1+28 (Pink)

View 9 Replies View Related

Highlight Row Range - Conditional Formatting

Apr 28, 2007

I have a spreadsheet of flight schedules (it has person's name and other details). How do you highlight the row within the table when a cell in the row has today's date and another condition when date is today+1

View 5 Replies View Related

Conditional Formatting: Format The Range

Jul 5, 2007

I have a range A1:A20. Each cell in this range contains a number between 1 and 100. I'd like to be able to format this range so that the top 3 or largest 3 numbers appear in red.

View 3 Replies View Related

Single Conditional Array Formula To Sum Occurrences In Range Of Cells

Mar 28, 2014

I'm trying to find a single conditional formula which summarizes multiple text occurrences in a range of cells. I managed to summarize the occurrences in a single cell, but my challenge is that I'm looking for a single (array?) formula for this.

I have a worksheet with column A which contains the condition and column B which contains a text field. I'm looking for a single formula to summarize all the 'AB' occurrences in the cells B4..B11 where the column A is Apple. I managed to do this by using an additional column (column C), however as my worksheet contains over 10000 rows, I would like to use a single conditional (array?) formula summarize the AB occurrences (multiple ' AB's can occur in a single cell) in stead of having to use an additional field per row.

I have attached a sheet as an example. I'm looking for a single array formula in cell C11 which summarizes the 'AB' occurrences in cells B4..B11 where column A is Apple.

View 3 Replies View Related

Conditional Formatting That Checks Every Cell In Range?

Dec 6, 2013

So I've applied conditional formatting to columns A and B to highlight and bold any blank entries. However since those guys tend to show up a thousand rows or so down I don't have anything to alert me that there is a problem. I know that I can just filter those columns to check but I'm not the only one using this sheet so I'd really like to have a visual cue.

Is there a way to have cell J1 highlight if any of the cells in the range (A2:H3000) have been bolded?

View 1 Replies View Related

Conditional Formatting / Offset - Highlighting Range?

Feb 2, 2013

I am using this Formula =Sum(Offset(A1,,,D1)) where I put for example number 4 in D1 and I get the sum starting cell A1 till Cell A4

I want the range to be highlighted as well, but I don't know how to do it.

View 1 Replies View Related

Conditional Formatting To Change Outside Border Of Range Only?

Oct 24, 2013

I'm trying to use conditional formatting to colour the border of a range of cells when another cell has data in it.

I can get it to color all the cells borders within that range when there's data in that cell but I just want the outside of the entire range.

View 4 Replies View Related

Conditional Formatting Of Range Of Variable Length

Apr 30, 2009

I have an issue regarding use of conditional formatting where the range i wish to format is on variable length.

For example; lets say i want to format F15 downwards depending on there being a value in the cell, how could I do that ?

Lets say I want the cell to turn GREY if number is 0, RED if the number is below 85, GREEN if above 95 and AMBER otherwise.

How could I do that ?
Any how could i ensure it picks all rows in the range where numbers have been added

View 7 Replies View Related

Conditional Formatting Dependant On A Range Of Cells

May 21, 2009

On attachment, I am trying to highlight values in column AA dependant on any values appearing in range AF4: AQ8 less 15 minutes. eg at present the value in AA29 is 13:22. I would like this cell to highlight as it is greater than 15 minutes before AH4 at 13:25.

The purpose of the sheet is to highlight any times in column AA that fall within 15 minutes of the start of a major meeting. These major meetings are pulled from another sheet, and a drop down box is used on that other sheet that then transposes the major meeting times to this sheet. I have tried an "Or' statement in a conditional format, however an error message appears around not using a range in a conditional format.

View 2 Replies View Related

Conditional Formatting Of Named Range Query?

Feb 27, 2012

I have a spreadsheet with several worksheets in it. The main sheet contains a calendar view that is fed from a separate sheet with holiday date ranges. This aspect works well and displays correctly in the main calendar view.

I am now adding in additional conditional formatting to each cell. As a first example i want the calendar to display bank holiday days by colouring the cell (lets say black). I am using conditional formatting only and have used the following formula;


Holidays is a named range, separate sheet (A2:A13).
C6 is the date value field (runs C6 through AN6 - perpetual calendar)

It evaluates the cell (i can see, TRUE,FALSE,FALSE,FALSE,...) and will only shade the cell if the date value field is equal to the first date in the Holidays named range. if this is not the case (FALSE,TRUE,FALSE,FALSE,...) the field value is set to "0".

how i can change this, entered as conditional format formula, to set the cell to "1" if any are evaluated as TRUE regardless of where they come in the name named range.

I want to additionally add in, from separate worksheets in the same workbook, face to face meetings and conference calls. I have assumed that i will use the same method to do this as Bank Holidays.

View 4 Replies View Related

Conditional Formatting Applied To Range And Then Copied?

Feb 22, 2013

I have applied conditional formatting to a range =$F$1:$J$44 based on the contents of a single cell =$L$44="No"

Now, I want to copy the whole range, including L44 to create another range controlled by L88, for example, but the $ signs are preventing that with the new range still being controlled by L44.

Is there a way to easily copy this whole range, including the CF, as I need to copy it many times down the page and I don't want to have to edit the "L44" cell each time.

View 1 Replies View Related

Conditional Formatting Over Wide Range Of Cells?

Mar 18, 2013

I am looking to write 'conditional format' to only highlight cells that are 2 greater than the cell in the previous column.


For instance:- cell F14 (which is 2 bigger than E14) and H23 (which is 2 greater than G23).

View 5 Replies View Related

Excel 2013 :: Conditional Formatting Twice And Over A Range?

Dec 26, 2013

Using Excel 2013.

I have two columns of numbers. For a row, if the right number is greater than the left number, then color it green, if it is less then color it red.


A1 = 100 B1 = 101, then B1 gets colored green
A2 = 100 B2 = 99, then B2 gets colored red


I have clicked on conditional formatting with A2 selected, then chose A1 for values greater than and chose green. I then copied the formula for all rows. I then did the same for values less than and chose red.

It is not coloring the cells correctly. On some rows when B

View 9 Replies View Related

Conditional Formatting Based On Range Size?

Jan 10, 2014

I have created a speadsheet for a tournament that lists the team names down column A (leaving a heading row). I have a formula in the top row which copies each row heading to a column heading 2 columns wide (to be able to record a result)


This way it will be possible to keep the results of the tournament on a grid. I would like to put in conditional formatting that meets 3 criteria...

1, it must fill every second row but only fill cells that have column and row headings (ie. rows that will have content in them which will be determined by the number of entrants). eg. if i have 10 teams then every odd row from row 1 to 11 will be filled. If 40 teams register then every odd row from 1 to 41 will be filled. 2, the fill needs to only fill cells that have column and row headings eg. if 10 teams register then every odd row from column 1 to 21 will be filled. Then 3, If the row heading and column heading are equal then it need to gray out or fill a different colour (or lock cell contents to empty if possible) but again, only fill cells that have column and row headings.

I have 2 rules.


It needs to only fill cells that have a column and row heading which it doesn't, it fills the correct cells untill there is no column heading then fills every other cell in columns beyond the last column with a heading.

Rule 2: so far this works:


but when i try to restrict it to cells with row and column headings it doesn't work. This is what I had:


View 1 Replies View Related

Copyrights 2005-15, All rights reserved