Hi Alex,

Have a look at the attached. I have assumed by the syntax of your original code 
that you are useing XL2007, so I've used that too.

One sheet does what you want by macro, while the second sheet does it with 
coditional formatting. The second sheet has the advantage of being active, in 
that the colours will change instantly if a cell in Col B becomes blank or 
non-blank. In the first sheet you have to run the macro again, or change it to 
an event macro.

Regards - Dave.
 


From: zanderman1...@googlemail.com
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Help needed in changing this code
Date: Mon, 16 Aug 2010 02:05:21 +0100






 
 
                         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

Attachment: Colours.xlsm
Description: Binary data

Reply via email to