Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 9th, 2011, 03:14 PM
Authorized User
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default Conditional Formatting with borders & colors

This is just excel, not VBA but I do not see a forum for "just" Excel.

I had hoped I could attach a file, or at least paste in a screenprint so this would be easier to show with a complete example.

I have a column of numbers with a total at the bottom. I used borders to put boxes around each cell, with the totals box in heavier lines. That is I but an "all sides" border on the entire list and total cells, then selected the totals cell and put a heavy border around it. (I tried to copy it below, but the paste does not show the black borders.) In my simple example/test I just put numbers 1 though 6 in successive rows, with one more row to hold the total.

Then I put conditional formatting in the cell with the number 6 so that any number over 5 had a red border (my own usage is a bit more complicated, but the issue is identical). In the spreadsheet (using Excel 2007), all the cells show the black border except the cell with the number 6. Inexplicably to me, that cell has red only on the two sides but black on the top and bottom lines. (Yes, I am sure that in the conditional formatting I selected all 4 sides for the red lines). Obviously, there is a conflict in that the cell with the 5 in it (as well as the one with the total) call for a black top and bottom, while the cell with the 6 in it calls for a red top and bottom. And the black line is taking precedence.

Is there a way to make the red border around the number 6, with the conditional formatting, take precedence for a complete red border on that cell?



To test my logic in this, I changed the conditional format to have a red border for every number over 4 and pasted the format into all the cells. That resulted in the cell with the 6 having red on 3 sides (all but the bottom) and the cell with the 5 in it having red on all 4 sides. So in this case, sometimes the black "won" and sometimes not.

So again, how can I make the red lines show on all 4 sides while cells above an below have their black lines only on 3 sides? If anyone wants to see the simple spreadsheet I made as an example, I am happy to forward it.

Last edited by rmccafferty; May 9th, 2011 at 03:25 PM..
 
Old May 9th, 2011, 10:40 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

hi robert,
a workaround would be that, instead of formatting a "box' around the numbers to sum, format three sides of them, i.e. the top and two sides, then the sum cell you format with the necessary black borders and conditional formatting





Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formatting r05579 SharePoint Development 0 February 19th, 2010 12:08 PM
conditional formatting jeremy1048 Access 4 July 3rd, 2008 11:20 AM
Conditional formatting vemaju Excel VBA 2 June 6th, 2007 04:41 AM
Conditional Formatting on Subform echovue Access 4 March 3rd, 2006 03:25 AM
Conditional Formatting Corey Access 1 November 8th, 2005 12:18 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.