Absolutely perfect mate :P may thanks Alex cargill
-----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Kishan Reddy, K Sent: 16 August 2010 08:13 To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Re: Help needed in changing this code Just Try this code. Dim CELL As Variant Range("B2:B105").Select For Each CELL In Selection If IsEmpty(CELL) Then With CELL.Offset(0, -1).Interior .ColorIndex = 3 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Else With CELL.Interior .ColorIndex = 10 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If Next You may need to change the colorindex as per your pallate. Regards, Kishan Reddy, K kishanreddy.kethire...@gmail.com On Aug 16, 6:05 am, Alexander Cargill <zanderman1...@googlemail.com> wrote: > Hi all > > I have been working on the following piece of code to identify Blank > Cells and turn them red: > > Range("B2:B105").Select > > Selection.SpecialCells(xlCellTypeBlanks).Select > > ActiveWindow.SmallScroll ToRight:=-1 > > With Selection.Interior > > .Pattern = xlSolid > > .PatternColorIndex = xlAutomatic > > .Color = 255 > > .TintAndShade = 0 > > .PatternTintAndShade = 0 > > End With > > Range("B15").Select > > I would like to expand the code to do the following: > > 1 expand this code so it searches the B column but instead off turning > the Blank cells in column B red it turns the cells Ajacent to the > blanks in column A Red > > 2 I would like to turn the cells that are adjacent to the cells that > are not blank green > > If you could help with this I would be most greatful > > Alex Cargill -- ---------------------------------------------------------------------------- ------ 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe