RE: $$Excel-Macros$$ VBA Conditional Format Offset
Try the Attachement -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Daniel Sent: Thursday, July 21, 2011 9:36 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ VBA Conditional Format Offset Try : Sub ConFormatOffset() With Range([A1], Cells(Rows.Count, 1).End(xlUp)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Operator:=xlGreater, _ Formula1:==A1B1 .FormatConditions(1).Interior.Color = RGB(255, 0, 0) End With End Sub Daniel -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de Natron Envoyé : jeudi 21 juillet 2011 04:15 À : MS EXCEL AND VBA MACROS Objet : $$Excel-Macros$$ VBA Conditional Format Offset I have multiple columns (around 140) of data and need to conditionally format the data as apposed to looping through. For instance I have the following data in column A and B. A B 1 10 2 1 8 4 4 3 5 6 Here is basically what I'm trying to do: If Column A has a value Greater than Column B then change Column A cell interior color to red My attempt in a Macro to perform this task is Below...not quite working Sub ConFormatOffset() With Selection Debug.Print ActiveCell Debug.Print ActiveCell.Offset(0, 2) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Operator:=xlGreater, Formula1:==A1Offset(ActiveCell,0,2) .FormatConditions(1).Interior.Color = RGB(255, 0, 0) End With End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Conditonal.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ VBA Conditional Format Offset
Try : Sub ConFormatOffset() With Range([A1], Cells(Rows.Count, 1).End(xlUp)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Operator:=xlGreater, _ Formula1:==A1B1 .FormatConditions(1).Interior.Color = RGB(255, 0, 0) End With End Sub Daniel -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de Natron Envoyé : jeudi 21 juillet 2011 04:15 À : MS EXCEL AND VBA MACROS Objet : $$Excel-Macros$$ VBA Conditional Format Offset I have multiple columns (around 140) of data and need to conditionally format the data as apposed to looping through. For instance I have the following data in column A and B. A B 1 10 2 1 8 4 4 3 5 6 Here is basically what I'm trying to do: If Column A has a value Greater than Column B then change Column A cell interior color to red My attempt in a Macro to perform this task is Below...not quite working Sub ConFormatOffset() With Selection Debug.Print ActiveCell Debug.Print ActiveCell.Offset(0, 2) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Operator:=xlGreater, Formula1:==A1Offset(ActiveCell,0,2) .FormatConditions(1).Interior.Color = RGB(255, 0, 0) End With End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ VBA Conditional Format Offset
Thanks Daniel that works, but I'm still trying to wrap my head around applying this format to various ranges on the worksheet. My data I'm comparing is in Column D and F, E and Getc all the way up to Column EK. Any pointers on skipping around with this formula? Thanks again! Natron Try : Sub ConFormatOffset() With Range([A1], Cells(Rows.Count, 1).End(xlUp)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Operator:=xlGreater, _ Formula1:==A1B1 .FormatConditions(1).Interior.Color = RGB(255, 0, 0) End With End Sub Daniel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ VBA Conditional Format Offset
Try : Sub ConFormatOffset3() Dim LastRow As Long LastRow = Cells.Find(*, , , , xlByRows, xlPrevious).Row With Range([D1], Cells(LastRow, EJ)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:==AND(F1,D1F1) .FormatConditions(1).Interior.Color = RGB(255, 0, 0) End With End Sub Note that the formula has to be written in your local version of Excel. As far as I am concerned, I have to translate it in French. Regards. Daniel -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de Natron Envoyé : jeudi 21 juillet 2011 19:05 À : MS EXCEL AND VBA MACROS Objet : Re: $$Excel-Macros$$ VBA Conditional Format Offset Thanks Daniel that works, but I'm still trying to wrap my head around applying this format to various ranges on the worksheet. My data I'm comparing is in Column D and F, E and Getc all the way up to Column EK. Any pointers on skipping around with this formula? Thanks again! Natron Try : Sub ConFormatOffset() With Range([A1], Cells(Rows.Count, 1).End(xlUp)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Operator:=xlGreater, _ Formula1:==A1B1 .FormatConditions(1).Interior.Color = RGB(255, 0, 0) End With End Sub Daniel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ VBA Conditional Format Offset
I have multiple columns (around 140) of data and need to conditionally format the data as apposed to looping through. For instance I have the following data in column A and B. A B 1 10 2 1 8 4 4 3 5 6 Here is basically what I'm trying to do: If Column A has a value Greater than Column B then change Column A cell interior color to red My attempt in a Macro to perform this task is Below...not quite working Sub ConFormatOffset() With Selection Debug.Print ActiveCell Debug.Print ActiveCell.Offset(0, 2) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Operator:=xlGreater, Formula1:==A1Offset(ActiveCell,0,2) .FormatConditions(1).Interior.Color = RGB(255, 0, 0) End With End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
