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

View 4 Replies


ADVERTISEMENT

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.

View 5 Replies View Related

Using Conditional Formatting To Format Range Based On One Cell

May 20, 2009

I am working with the Conditional Formatting, which is fine for one cell. Here is what I am trying to do: IF cell in $A1 = 1 then bold $B2:$M2 and apply solid line border to top of cell ranges. I have tried conditional formatting but it only formats the cells in column A. And I can't seem to find a BOLD statement for the cell formulas.

View 2 Replies View Related

Conditional Format Based On A Text Range Within A Cell

Nov 11, 2009

conditional format formula that is based on a text range within a cell. In my case, I would like to compare the low and high range when inputted as a number followed by the quotation marks followed by space then hyphen, space then number and ending in quotation marks. The quotation mark is being used to represent inches.

Like this in cell D14: 0.2 - 2.2

I am using the following condition format formula which works for the strict case above.

HTML =OR(D16<LEFT($D$14,3)+0,D16>MID($D$14,8,3)+0)
However, there will be times when users using this spreadsheet may leave out the spaces on either side of the hyphen, or add more than one space between the number and hyphen. Also if additional digits are added, my formula above does not include all the digits in the results. I prefer to leave the quotation marks in...........

View 9 Replies View Related

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.

View 3 Replies View Related

Conditional Format Rows 4+ Criteria

Mar 7, 2007

What I am looking to do is format a row so that it is highlighted if one cell in that row meets one of two criteria or if another cell in that row meets one of two criteria: I was hoping to use the excel conditional formating tool but I'm stumped as I need 4 criteria and it can only do 3 criteria.

The cells in question require a greater than or less than criteria. If could use something like: Formular is =$AJ16 <= -0.11 or =$AJ16 >= 0.11 and
Formular is =$AK16 <= -0.11 or =$AK16 >= 0.11 I would be very happy but it won't let me do this - is there a way it can be done?

View 2 Replies View Related

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.

View 9 Replies View Related

Conditional Format Based On 2 Criteria Occurring

Oct 9, 2012

I need contents in cell A5 to turn red based on 2 criteria occurring.

IFCell F5 is greater than 0 (zero) AND IF date in H5 is equal to or less than 30 from today's date. PS: if needed I have today's date in A1 for reference.

Can this be done and how?

View 2 Replies View Related

Insert Rows & Repeat Values Based On Corresponding Cell Value

Jul 7, 2009

I have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above.

For example - before macro:

Joe Bloggs 2
Adam Wilson 10
Peter Andrews 0
Claire Burrows 6

After macro:

Joe Bloggs
Joe Bloggs
Adam Wilson
Adam Wilson
Adam Wilson
Adam Wilson....................................

View 6 Replies View Related

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

View 8 Replies View Related

Conditional Format (with Formulas) 10 Cells Depending On 1 Criteria

Oct 19, 2009

I want to colour 10 cells (A1-J1) if I type in yes in cell K1. Using conditional formatting Im only able to colour 1 cell. Is this possible without using VBA?

View 5 Replies View Related

Format Color For Range Of Cells Based On Cell Value

Jul 1, 2008

I have been tasked with creating a Macro in order to help speed up a rather simple set of steps that are taken to format a spreadsheet each day. The last step, however, is not so simple. I would like to add a statement to the macro code that does that following:

Based on the value of a specific cell, color the cell and all corresponding cells within the same row (from colums A to AA) yellow. Essentially, cells in column D may have a the value "No Allocation", and if they do, I will need to format that cell yellow as well as all the cells within the same row for the colume range A to AA.

View 9 Replies View Related

Reformat Table Layout To Repeat Rows Based On Cell Values

Jun 11, 2008

I can export inventory data from my Point of Sale system that looks like this:

I need to manipulate it to look like this:

To do so I think I need a macro(?) to:

Copy and insert as many rows as the quantity in Column C. In case of >1 the row is deleted.

This would allow me to print labels for every item in my inventory.

View 3 Replies View Related

Conditional Format Over Range Of Rows

Feb 25, 2008

I've been trying to get around the 3 rule limit for conditional formatting,
and I've found the code I need on the site

[url]

I've changed it to suit my needs

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("A1:A2000")) Is Nothing Then
Select Case Target
Case Is = "Test"
icolor = 6
Case Is = "Test2"
icolor = 12
Case Is = "Test3"
icolor = 7
Case Is = "Test4"
icolor = 53
Case Is = "Test5"
icolor = 15
Case Is = "Test6"
icolor = 42
Case Else
End Select

Target.Interior.ColorIndex = icolor

End If

End Sub

This works great for one cell, but I need to format a row range based on the single cell.
So if Test is entered into A1, then it changes to yellow, I also need the next 6 cells on the row to change too- In this case [B1:G1].

But it needs to cover the range in the code A1:A2000

View 9 Replies View Related

Insert Range Of Cells Based On Cell Criteria?

Apr 1, 2014

I will have a list of unique values. For each unique value, there is an indicator that tells me how many lines of information I need. Based on that criteria, I need a macro to insert that many rows and populate it with text. This will ultimately become a look up table that I will just use index/match function to populate.

I've attached a sample sheet. I'm not sure if I need a macro to insert rows or a macro to paste a copied range. Or maybe I need a mixture of both with a formula to calculate the number of cells I need?

View 9 Replies View Related

Move Range Of Cells Based On Cell Criteria

Nov 16, 2007

I'm trying to do is loop through a range of cells, and if the cell meets a condition I want to move it and 2 cells to the right of it left by one cell.

Sub MoveRangeOfCellsBasedOnCellCriteria()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("H2", Range("H65536").End(xlUp))
For Each cell In myrange
If IsNumeric(Left(cell.Value, 1)) _
Or Left(cell.Value, 5) = "UNIT " _
Or Left(cell.Value, 4) = "THE " _
Or Left(cell.Value, 5) = "FLAT " Then
Else
cell.Range("A1:C1").Select
Selection.Cut
cell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
cell.Offset(1, 1).Range("A1").Select
End If
Next cell
End Sub

View 7 Replies View Related

Conditional Formatting To Colour One Cell Based On Text From Range Of Cells?

Oct 22, 2013

what i would like to do is change the fill colour of D68 if the word Air appears with in D5:D65?

View 6 Replies View Related

Conditional Format Range Based On Date

Sep 8, 2009

I have a monthly calender, with each month on its own sheet. I have a sheet named Holidays, which list the holidays. I have been able to use conditional format and highlight the dates in the months the holidays fall on, but I would like to highlight a range of cells below the date on monthly sheet. I have attached a sample of what I have and would like to achieve. I am not much good at using VBA, but would not be against using it either.

View 2 Replies View Related

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

View 3 Replies View Related

Conditional Format Range Based On Numbers Present In Another

Jan 18, 2008

I need to check a range of cells for 2 different values. If either value is present, I need to shade a cell (outside the original range) red. If neither value is present I need to shade the cell yellow. Can this be accomplished with conditional formatting?

View 2 Replies View Related

Conditional Format Column / Cell That Don't Meet Criteria

Mar 19, 2014

I am trying to conditionally format cells in column I (named range of ETID).

I want the cell to change shading if the cells do NOT meet this criteria:

Whole Number10 digitsstart with '0' or '1'I have this but it isn't working:

=AND(OR(LEFT($i2,1)="0",(LEFT($i2,1),1)="1"),OR(LEN($I2)<>10,($I2)<>""))

View 4 Replies View Related

Excel 2010 :: 3+ Conditional Formatting Rules For Cell Range Based On Formula From Other Cells

Feb 5, 2013

how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:

For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on

The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.

resource tracking ex.jpg

View 1 Replies View Related

Conditional Formatting To Format Cells On Several Rows Below According To The Day In 2007

Sep 8, 2009

Im having aproblem with Excel 2007 about Conditional Formatting. I have a row of Dates for example 02-01-2009 03-01-2009 04-01-2009 , etc in different columns.

Then what I want to do is use Conditional Formatting to Format cells on several rows below according to the day (if its weekend paint red, if not, dont do anything). I'm using the "Use a formula to determine which cells to format" and the condition is (supposing the cells with dates are A1 to C1)
=WEEKDAY(A1:C1) > 5 .

So with those 3 dates provided lets suppose Januar2nd is not a weekday, so the outputIwant is: Red White White, (Next Row) Red White White, etc for several rows.

Now what I think is not right is it only works for the row in which I have the cursor so its like: (Lets imagine I selected 3 rows on which I want to see that output) Red White White (The row in which the cursor is works fine), but the next row goes Red Red Red and the third the same.

View 5 Replies View Related

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.

View 8 Replies View Related

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

View 3 Replies View Related

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

View 2 Replies View Related

Repeat Row Based On Text Criteria

May 29, 2007

to run a macro or any other methods to repeat the same head rows in my database as followings:

Let's say the main head row is in row 1.

Item names such as Item A; Item B; Item C ....in column A

What I want is to insert the same head row after the list of each item.

Item name (head row)
Book A
Book A
Book A
Item name (head row to be repeated)
Book B
Book B
Item name (head row to be repeated)
Book C
Book C

View 9 Replies View Related

Count Conditional Format Cells In Date Range

Apr 30, 2009

Hello, we have a stop light spreadsheet for status with several different projects. In each row there are conditional format status green - good, yellow - needs work, red - bad...based on data from another tab.

There is an "overall status" column that pulls the worst color that exists for a given row.

=IF(COUNTIF(F1:V1,"r")>0,"R",IF(COUNTIF(F1:V1,"y")>0,"Y",IF(COUNTIF(F1:V1,"g")>0,"G","")))

There is also a date for each row. Now we want to pull the worst color in the "overall status" column for a date range...and display that on a separate summary tab.

For 4/1/09 thru 4/15/09 - pull the worst color from the "overall status" column from that date range.

View 10 Replies View Related

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.

View 6 Replies View Related

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.

View 9 Replies View Related







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