# Conditional Formatting With R1C1 References

Aug 19, 2007Does anyone know if it's possible to use R1C1 style references in conditional formatting formulas?

Eg., =R[-1]C > 1

I tried the above and I keep getting an error.

Does anyone know if it's possible to use R1C1 style references in conditional formatting formulas?

Eg., =R[-1]C > 1

I tried the above and I keep getting an error.

ADVERTISEMENT

I have 2 sheets. Sheet1 looks like this:

B

D

A

[Code]....

Note: The "X"s are actually not in the cells, they are for illustration purposes only. Instead of the "X"s, I want to fill the cells marked with X with the color red. The cells in sheet2 have no value except row 1 and column 1.

I am looking for the formula to fill into the conditonal formatting field for sheet2.

The formula should detect how many letters there are in row 1 of sheet2 (for example here we have 4, namely A, B, C and D). If I add a new column E later, I just want to re-apply the formula to those new cells, not edit the formula itself. If there is a new column E, it will also be added on sheet1, but not necessaryly to the right side of the column with the header "A", maybe it will be added between "B" and "D".

The number of rows will not change, they stay static at 1-5

Here is the underlying task.

Sheet2: A column that has all days of the year, from Jan 01 to Dec 31, from top to bottom, starting in A2 going down to a366. In column B, C, D and so on, in row 1, I write the names of different countries. Sheet1: In row 1 I write the same country names as in sheet2, but not in the same order. Below the country names I list the official pulic holidays of the whole year.

I need the formula for conditional formatting that will color the intersecting cells of Country names and days of the year. And I need the formuly to be dynamic so that I can add more countries later without have to adapt the formula for new columns manually.

So far I have the formula to count the filled cells in row 1 on sheet1. I also have the formula to find the country name in sheet2 and return the column number of the same country in sheet1. Last but not least, I can make this work with a static formula, but replacing the static pieces with the dynamic pieces mentioned above just won't format the cells as desired.

I am working in a receiving room at a condo complex and am trying to facilitate how we handle and distribute the packages.

What I have in the first sheet of my would-be file is the unit numbers in a 28X12 array (12 units on 28 floors). I would like to make the array conditionally formatted to be highlighted in red to show that there is a package in for the unit in question. On the next sheet, I am putting information such as carrier, unit number and tracking number for each of the packages we receive. I want the unit number on the first sheet to immediately be red when the unit number is typed in the second sheet. When doing conditional formatting, it is easy to do this, by making the condition that the unit in the array will be highlighted when the unit is seen in the unit column in the second sheet.

My question is this: is it possible to somehow duplicate the references for the entire column that contains the unit numbers. I want a whole column to be the reference for conditional formatting of an array, so that I would be easily able to see who has a package in the receiving room, and who does not.

I am trying to use conditional formatting in Excel 2003 to indicate when certain tasks are overdue (by highlighting the cell in red). My problem is that I have multiple criteria. If my line item is a "priority" item, it is considered aged if not resolved after 2 days; if the line item is "routine", the item is not considered aged until after two weeks (14 days). My goal is:

1) I would like the "status" cell in column D (which says "open" or "closed") to turn red if the item is aged.

2) Aging depends on the "priority" status in column A .......

I'm having trouble with Color Scales within Conditional Formatting. I have a data set of commodity prices. In column A I have the name of the commodity, in column B I have the standard deviation of the price change of the commodity, and in Columns C-N I have the monthly % change in the commodity price. I want to conditionally format with Color Scales each row of price changes within Columns C-N based on each commodity's standard deviation (column B). If the price change is a one standard deviation or more decrease, I want the cell to be dark red; if the price change is less than a one standard deviation decrease, I want the cell to be a gradient of light red; if the price change is a one standard deviation or more increase, I want the cell to be dark blue; if the standard deviation is less than a one standard deviation decrease, I want the cell to be a gradient of light blue; and if the price change is 0, then I want the cell to be white.

I can achieve this perfectly by manually doing 3-Color Scale Conditional Formatting for each row, but it's very time-consuming. And Excel doesn't allow me to enter relative cell references when I'm doing the Color Scale Formatting. Is there a quick way that I can do this so that each row is color formatted differently?

I've attached an example file (there are many more rows within the original file) and formatted the first several rows manually as I want the final product to look.

example.xlsx

I have this fairly simple formula which decides whether to shade a cell or not

=AND($X$1<>"TBD",R3<>"None",AC3="Y")

This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003.

Is it possible to title a graph according to a changing column name perhaps?

View 5 Replies View RelatedI am trying to use a conditional sum formula where the data resides in and external workbook. This is fine until I close the external workbook and the formula no longer shows the external reference. Is there a way of getting excel to use conditional sums in this way (or what is the best way of achieving this?)

View 4 Replies View RelatedI have inherited a monstrous speadsheet on which all data appears on a single sheet rather than being broken up across several sheets in some logical fashion.

One portion of this monster is a set of quite a few form letters, each of which reference various cells elsewhere in the spreadsheet.

I want to move all of these letters, together, to another tabbed sheet as a first step in reorganizing the monster.

Simply copying and pasting the cells or columns doesn't work. It fails in different ways depending on which pasting options I employ.

A very simplified version of my problem appears on the 5 sheets of the attached file, with what I hope is just enough further detail about the difficulty.

By the way, I also tried using the "Insert Copied Cells" option when pasting but since this failed with the exact same results as one of the other options I didn't include the results in the example shreadsheet.

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

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

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.

Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...

View 9 Replies View RelatedI need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.

View 2 Replies View RelatedI have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.

I have been working on this for a couple of days and even tried EE, but to no success.

I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

On Error GoTo mEnd

Set rng = Sheets("Log").[F14:F10000]

If Not Intersect(rng, Target) Is Nothing Then

If Target = "" Then

With Sheets("Log")

I have two tabs that reference the function ConcatenateIf(), defined by me, see below:

Function ConcatenateIf(iRange As Range, iLook As String, iNum As Integer)

For Each cell In iRange

If cell.Value <> iLook Then

ConcatenateIf = ConcatenateIf & Chr$(10) & cell.Offset(0, iNum).Value

End If

Next cell

End Function

Works great. But then, I copy one of the tabs to make a third tab, and suddenly every cell in all three spreadsheets that references ConcatenateIf displays "#NAME?".

I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.

All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.

=COUNTIF(E14:Z14,"*Y*")

=COUNTIF(E11:Z11,"*DI*")

It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.

I have tried to do it with structured references and with cell references I get a column of zeros!

I am using the dsum formula to sum some values...the formula in B2 is:

=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))

The Planes Entidades sheet the data is layed out like this: ....

=IF(CEILING(B9/0.5,1)>40,MAX(CEILING(B9/0.55,1),40),MAX(CEILING(B9/0.5,1),36))-0.01

Edit B9 to the R1C1 style of cell reference, how to do that?

I have tried to reset the cell reference style back to A1 from R1C1 (Tools-->Options-->General tab-->uncheck R1C1 reference style), but when I close Excel and reopen it, the reference style changes back to R1C1 style. When I make the change I described above using the Options feature, it changes it for the current file and saves it with the file. When creating a new file in the same session, it keeps the A1 reference style, but when I close Excel and reopen it, it returns to the R1C1 style. Any ideas about how to return to the default A1 reference style for the next Excel session?

I get an error at the line in red, I'm sure I messed up the R1C1 Part. The first formula will be put in HZ5:HZ100 Those cells need to reference another sheet in the workbook call Varsity A3:A98. The Second formula will be IA5:IA100 Will ref Varsity B3:B98

So whenever someone enters a name in the varsity sheet the name will appear in the other sheet. This is a master workbook That will be copied many times that is why I am putting the formula in after I create the copy because the links would look at the original Varsity sheet otherwise then the user get the update links messages.

I am trying to insert a formula into a VBA Code but I get an error msg.

The formula is;

It's probably O so simple but: Using a variable in R1C1, here's the attempt:

-----------------------------

Dim RS_Period As Double

-----------------------------

RS_Period = Sheets("Sheet1").Range("B17") - 1

-----------------------------

Range("E2:E" & LastRow).FormulaR1C1 = "=(RC[-2]-OFFSET(RC[-2],9,0))/OFFSET(RC[-2],9,0)"

-----------------------------

I lieu of the "9" I need to use the "RS_Period" which is an offset value.

I need to know how to use a variable in an R1C1 statement. The statement is as follows:

Set rng = Sheet1.Range("A2:QFinalRowII").CurrentRegion

Ideally the statement would read something like,

Set rng = Sheet1.Range("A2:Q10").CurrentRegion

How would I use a variable name like "FinalRowII" instead of a number?

I want to subtract one date from another to find out the total waiting time, and repeat this action for multiple rows.

I'm trying to use an R1C1 formula within all of this.

Dim TotalTimes As Long

Dim iTime As Integer

TotalTimes = Cells(Rows.Count, 4).End(xlUp).Row

For iTime = 1 To TotalTimes

Cells.Find("Waiting Time").Offset(iTime, 0).FormulaR1C1 = [R[iTime]C[-1]-R[iTime]C[-2]]

I'm trying to figure out how many Widget As are in Order 0001, for example.

Order # Product

0001 Widget B

0001 Widget A

0001 Widget A

0001 Widget C

0001 Widget A

0002 etc.

I would think that I should use a sum(R1C1:R1C1) format to figure this out, but I might be making this harder than necessary.

In a column, there are 4 option for each cell -

"PAYMENT DUE"

"PAID"

"UNDERPAID BY..."

'OVERPAID BY..."

These are filled in by a formula

I would like to make them 4 different colours but excel only allows 3 options

I have a hard time when it comes to "If then" statements and conditional formatting. I have some data that I am collecting and entering into a spreadsheet for a couple different companies. Once I receive paperwork from those companies for the data collected I mark the data with a blue colored text. I would like to have a second sheet that generates what companies have not sent me their paperwork. "If the data is black text, then generate <name of company> on second sheet." The list is small right now, but as the project continues it will grow very large and it will become difficult to track. I will attach a copy of the spreadsheet for you to see the situation. I would like to learn how to do these types of formulas, is there a tutorial geared towards these types of situations?

View 2 Replies View RelatedI seem to have a problem using conditional formatting with Vba in Excel 2003

When I run -

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