Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Conditional Formatting in VB
Hi there,
I need to add a set of formats to a column but based on IF statements. For example, I have column A (Date Raised) and column I (Date Acknowledged), I want to be able to colour the cells based on what date was added to column I e.g: If I <= (A+2) then cell is green If I (A+2) then cell is red with white text If I is null then ignore Can someone help with some code please. Thanks in advance!!! Mav |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Conditional Formatting in VB
Hi,
Select the data in column I and try this Format|Conditional Format Select formula is enter the formula =AND(I1<"",I1<=A1+2) Click Format|Patterns and select green =AND(I1<"",I1A1+2) and select red with white text Mike Click Add Repeat the above but use the formula "Maver1ck666" wrote: Hi there, I need to add a set of formats to a column but based on IF statements. For example, I have column A (Date Raised) and column I (Date Acknowledged), I want to be able to colour the cells based on what date was added to column I e.g: If I <= (A+2) then cell is green If I (A+2) then cell is red with white text If I is null then ignore Can someone help with some code please. Thanks in advance!!! Mav |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Conditional Formatting in VB
Hi Mike,
Thanks for that but I really need to know how to do this in VB as I have about 6 different conditions I need. Sorry, I thought I had put that in but obviously not. Mav "Mike H" wrote: Hi, Select the data in column I and try this Format|Conditional Format Select formula is enter the formula =AND(I1<"",I1<=A1+2) Click Format|Patterns and select green =AND(I1<"",I1A1+2) and select red with white text Mike Click Add Repeat the above but use the formula "Maver1ck666" wrote: Hi there, I need to add a set of formats to a column but based on IF statements. For example, I have column A (Date Raised) and column I (Date Acknowledged), I want to be able to colour the cells based on what date was added to column I e.g: If I <= (A+2) then cell is green If I (A+2) then cell is red with white text If I is null then ignore Can someone help with some code please. Thanks in advance!!! Mav |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Conditional Formatting in VB
Hi,
This works on the Range i1 to I 20 so change to suit Sub sonic() Sheets("Sheet1").Range("I1:I20").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(I1<"""",I1<=A1+2)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(I1<"""",I1A1+2)" Selection.FormatConditions(2).Interior.ColorIndex = 3 Range("I1").Select End Sub Mike "Maver1ck666" wrote: Hi Mike, Thanks for that but I really need to know how to do this in VB as I have about 6 different conditions I need. Sorry, I thought I had put that in but obviously not. Mav "Mike H" wrote: Hi, Select the data in column I and try this Format|Conditional Format Select formula is enter the formula =AND(I1<"",I1<=A1+2) Click Format|Patterns and select green =AND(I1<"",I1A1+2) and select red with white text Mike Click Add Repeat the above but use the formula "Maver1ck666" wrote: Hi there, I need to add a set of formats to a column but based on IF statements. For example, I have column A (Date Raised) and column I (Date Acknowledged), I want to be able to colour the cells based on what date was added to column I e.g: If I <= (A+2) then cell is green If I (A+2) then cell is red with white text If I is null then ignore Can someone help with some code please. Thanks in advance!!! Mav |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Conditional Formatting in VB
Hi,
I forgot the white font so add this to the second condition Selection.FormatConditions(1).Font.ColorIndex = 2 "Mike H" wrote: Hi, This works on the Range i1 to I 20 so change to suit Sub sonic() Sheets("Sheet1").Range("I1:I20").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(I1<"""",I1<=A1+2)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(I1<"""",I1A1+2)" Selection.FormatConditions(2).Interior.ColorIndex = 3 Range("I1").Select End Sub Mike "Maver1ck666" wrote: Hi Mike, Thanks for that but I really need to know how to do this in VB as I have about 6 different conditions I need. Sorry, I thought I had put that in but obviously not. Mav "Mike H" wrote: Hi, Select the data in column I and try this Format|Conditional Format Select formula is enter the formula =AND(I1<"",I1<=A1+2) Click Format|Patterns and select green =AND(I1<"",I1A1+2) and select red with white text Mike Click Add Repeat the above but use the formula "Maver1ck666" wrote: Hi there, I need to add a set of formats to a column but based on IF statements. For example, I have column A (Date Raised) and column I (Date Acknowledged), I want to be able to colour the cells based on what date was added to column I e.g: If I <= (A+2) then cell is green If I (A+2) then cell is red with white text If I is null then ignore Can someone help with some code please. Thanks in advance!!! Mav |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Conditional Formatting in VB
Sorry Mike,
I think im going mad. I have copied your code but it doesn't seem to update the cells. Any ideas please? Mav "Mike H" wrote: Hi, This works on the Range i1 to I 20 so change to suit Sub sonic() Sheets("Sheet1").Range("I1:I20").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(I1<"""",I1<=A1+2)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(I1<"""",I1A1+2)" Selection.FormatConditions(2).Interior.ColorIndex = 3 Range("I1").Select End Sub Mike "Maver1ck666" wrote: Hi Mike, Thanks for that but I really need to know how to do this in VB as I have about 6 different conditions I need. Sorry, I thought I had put that in but obviously not. Mav "Mike H" wrote: Hi, Select the data in column I and try this Format|Conditional Format Select formula is enter the formula =AND(I1<"",I1<=A1+2) Click Format|Patterns and select green =AND(I1<"",I1A1+2) and select red with white text Mike Click Add Repeat the above but use the formula "Maver1ck666" wrote: Hi there, I need to add a set of formats to a column but based on IF statements. For example, I have column A (Date Raised) and column I (Date Acknowledged), I want to be able to colour the cells based on what date was added to column I e.g: If I <= (A+2) then cell is green If I (A+2) then cell is red with white text If I is null then ignore Can someone help with some code please. Thanks in advance!!! Mav |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Conditional Formatting in VB
I wrote it so it could go in as worksheet code or in a module so I would
suggest you right click the sheet, view code and paste this in and try and run it again. I've added another line so I suggest you use this version. When you say cells aren't changing that may suggest that what you think are dates may not be so check they are. For a date in A1 type =a1+1 in another cell and see what you get, it should add 1 day to the date in a1 provided the date is really a date. lastly select I1 and click format|Confitional format and check if the macro has entered the formats etc. If it has then It's fairly certain you hava a data type issue. Sub sonic() Sheets("Sheet1").Range("I1:I20").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(I1<"""",I1<=A1+2)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(I1<"""",I1A1+2)" Selection.FormatConditions(2).Interior.ColorIndex = 3 Selection.FormatConditions(1).Font.ColorIndex = 2 Range("I1").Select End Sub Mike "Maver1ck666" wrote: Sorry Mike, I think im going mad. I have copied your code but it doesn't seem to update the cells. Any ideas please? Mav "Mike H" wrote: Hi, This works on the Range i1 to I 20 so change to suit Sub sonic() Sheets("Sheet1").Range("I1:I20").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(I1<"""",I1<=A1+2)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(I1<"""",I1A1+2)" Selection.FormatConditions(2).Interior.ColorIndex = 3 Range("I1").Select End Sub Mike "Maver1ck666" wrote: Hi Mike, Thanks for that but I really need to know how to do this in VB as I have about 6 different conditions I need. Sorry, I thought I had put that in but obviously not. Mav "Mike H" wrote: Hi, Select the data in column I and try this Format|Conditional Format Select formula is enter the formula =AND(I1<"",I1<=A1+2) Click Format|Patterns and select green =AND(I1<"",I1A1+2) and select red with white text Mike Click Add Repeat the above but use the formula "Maver1ck666" wrote: Hi there, I need to add a set of formats to a column but based on IF statements. For example, I have column A (Date Raised) and column I (Date Acknowledged), I want to be able to colour the cells based on what date was added to column I e.g: If I <= (A+2) then cell is green If I (A+2) then cell is red with white text If I is null then ignore Can someone help with some code please. Thanks in advance!!! Mav |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Conditional Formatting in VB
On Apr 18, 2:11*pm, Maver1ck666
wrote: Hi Mike, Thanks for that but I really need to know how to do this in VB as I have about 6 different conditions I need. Sorry, I thought I had put that in but obviously not. Mav "Mike H" wrote: Hi, Select the data in column I and try this Format|Conditional Format Select formula is enter the formula =AND(I1<"",I1<=A1+2) Click Format|Patterns and select green =AND(I1<"",I1A1+2) and select red with white text Mike Click Add Repeat the above but use the formula "Maver1ck666" wrote: Hi there, I need to add a set of formats to a column but based on IF statements. For example, I have column A (Date Raised) and column I (Date Acknowledged), I want to be able to colour the cells based on what date was added to column I e.g: If I <= (A+2) then cell is green If I (A+2) then cell is red with white text If I is null then ignore Can someone help with some code please. Thanks in advance!!! Mav- Hide quoted text - - Show quoted text - Hi, If you are using a version less than Excel 2007 then you can't have more than 4 conditions (1 default and 3 defined) in Conditional Formatting. Anant |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Conditional Formatting-HELP | Excel Discussion (Misc queries) | |||
Conditional Formatting with multiple spreadsheets | Excel Discussion (Misc queries) | |||
Multiple Conditional Formatting | Excel Discussion (Misc queries) | |||
multiple conditional formatting | Excel Worksheet Functions | |||
Multiple Conditional Formatting Using VBA | Excel Programming |