Macro To Add Conditional Formatting To Variable Range

Jun 16, 2008

I have 2 columns 'C' and 'D' which I want to apply Conditional Formatting to (i.e. colour the background of the cell in column 'D' for the respective row in colum 'C') if they contain different values. I have the following

Sub CellCCondFormatting()
Dim j As Long
Range("C2").Select
j = Range("C2"). CurrentRegion.Rows.Count
MsgBox ("1st: " & j)
Range("C2:C" & j).FormatConditions.Delete
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=D" & j
Range("C2:C" & j).FormatConditions(1).Interior.ColorIndex = 3
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=D" & j
Range("C2:C" & j).FormatConditions(2).Interior.ColorIndex = 4
MsgBox ("2nd pass: " & j)
End Sub

It iterates through all rows in my CurrentRegion OK but the Conditional Formatting 'formula' operates on the wrong value in column 'D'. For example, when viewed via menu option Format > Conditional Formatting... row 2 column 'D's Conditional Formatting value is D1714, row 3 column 'D's value is 'D1715' and so on....................

View 2 Replies


ADVERTISEMENT

Macro To Add Conditional Formatting Code To Variable Range

Jun 12, 2008

I am working with a large report that needs to be broken out and sent as separate files to recipients for confidentiality purposes. I'd rather not use views/protection since there are many different ways particular people need to see the data, plus it is a very large file and flattening it works to everyone's advantage. My goal is a macro that will copy each tab into separate workbooks, paste special values, and save as each as Cell A1 (or the tab name-same thing). I have tried recording macros and editing (I'm very new to VBA) many times but it's a mess.

View 2 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

Using Text Variable For Conditional Formatting

Mar 11, 2014

I'm trying to reverse engineer a spreadsheet (see attached) and apply the data table to my own spreadsheet. The inspiration spreadsheet (attached) utilizes a 2 variable data table and conditional formatting, where the cells are formatted based on their value in relation to a variable called "Gross". So the rules look something like this:

Cell Value Between 0 and Gross-1
Cell Value > Gross
Cell Value = Gross
Cell Value < 0
Graded Color Scale Minimum ="<385000" Maximum =">385000"

I assume the Gross is calculated as 25% * 350,000 = 87,500. The hard coded value of 385000 is actually incorrect, because it's based on a percentage of 10%... but I'll let that slide The resulting formatting of the data table would support the hypothesis that "Gross" = 87,500. However, when I reset the Conditional Formatting to reference another cell with the calculation result of 87,500 it completely throws off the formatting. I'm simply stumped as to how they are using the text "Gross" as a variable. How/where are such variables set so they can be used in conditional formatting?

How to apply the data table logic itself to another spreadsheet.

View 3 Replies View Related

Conditional Formatting, Variable Text

Mar 1, 2010

I can't figure out how to set up conditional formatting to let me know if the value of a text cell is NOT X or Y. In other words, the only appropriate responses are X or Y (or Y or N or whatever the case may be) and if it's NOT one of those two letters, I want to apply conditional
formatting. Also, will it accept either upper or lower case (I'm fine with it either way).

View 9 Replies View Related

Using Conditional Formatting To Fill Certain Cells Based On Variable?

May 23, 2013

The spreadsheet I've been working on is functionally working how I would like, but I am looking to improve it cosmetically. I have a range of cells in which I input scores. Those scores are averaged in blocks of the value of cell B1. I would like to fill the cells yellow if they are a multiple of the value of B1.

View 2 Replies View Related

Referencing Variable In Conditional Formatting But Applies To All Excel?

Jul 9, 2012

I have some VBA code that formats my sheets and works fine, but the issue is that I run this same piece of code more than once in different sheets with the only difference being the formula references a different cell.

' Green Cells indicate that the supervision is within the year
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=Datedif(D8,today(),""m"")

View 4 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

(=IF(AND(QSA>0,QSA=qty1a),assembly1,"")),

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.

QUOTETEMPLATEMACROTEST.xlsm

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
=AND($A$1<M2,$A$1>=(WORKDAY(M2,-3)))
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

Using An Array/range In Conditional Formatting

Feb 21, 2007

I have this :

ColumnRow
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 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

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 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;

IF(C6=Holidays,1,0)

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.

[IMG]capture.jpeg[/IMG]

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

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved