Setting colors/borders for cell

What is the VB code syntax for setting background colors and borde
style for a particular cell.  

Worksheets("Sheet1").Cells(1,1)???????? = ???????

.Borderstyle doesn't work in this syntax, does it?

thank

--
k48
-----------------------------------------------------------------------
k483's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1079
View this thread: http://www.excelforum.com/showthread.php?threadid=27427

0
11/1/2004 6:27:15 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
413 Views

Similar Articles

[PageSpeed] 18

Hi
simple solution: Record a macro while doing it manually :-)

e.g. background:
activecell.interior.colorindex=3


-- 
Regards
Frank Kabel
Frankfurt, Germany


k483 wrote:
> What is the VB code syntax for setting background colors and border
> style for a particular cell.
> 
> Worksheets("Sheet1").Cells(1,1)???????? = ???????
> 
> Borderstyle doesn't work in this syntax, does it?
> 
> thanks
0
frank.kabel (11126)
11/1/2004 6:41:11 PM
hi,
the command is like below
Worksheets("Sheet1").Cells(1,1).interior.colorindex = ?
to find the colorindex codes, in a blank workbook, copy, 
paste and run the following code.
-----------------------------------
Sub macGetColors()
' Macro written 2/10/01 by FSt1
    Sheets("Sheet1").Select
    Range("B2").Select
    Set ci = Range("A1")
    ci.Value = 1
    Set c = Range("B2")
    Do Until ci > 56
        Set c2 = c.Offset(1, 0)
        Set cnum = c.Offset(0, 1)
            c.Interior.ColorIndex = ci.Value
            c.Offset(0, 1) = ci.Value
            ci.Value = ci.Value + 1
            Set c = c2
            c.Select
    Loop       
End Sub
---------------------------------------------
borders get a little more complecated because there are so 
many.
Cell(1,1).select (or Range("a1").select)
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With

>-----Original Message-----
>
>What is the VB code syntax for setting background colors 
and border
>style for a particular cell.  
>
>Worksheets("Sheet1").Cells(1,1)???????? = ???????
>
>.Borderstyle doesn't work in this syntax, does it?
>
>thanks
>
>
>-- 
>k483
>----------------------------------------------------------
--------------
>k483's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=10791
>View this thread: 
http://www.excelforum.com/showthread.php?threadid=274270
>
>.
>
0
anonymous (74722)
11/1/2004 8:57:39 PM
Reply:

Similar Artilces:

Saving a Custom Color Palette
I would like to save customized color palettes for workbooks involved with image editing. Is there a way to do this in XL2000 other than defining a palette in a specific workbook... that's ok, but if you do a reset you loose your custom job! ? Thanks >-----Original Message----- >Bob, > >If you setup the custom palette with a new workbook open, and then save the >workbook as Book.xlt in the Excel startup directory, it will be with you >forever. > >-- > HTH > > ------- > > Bob Phillips > ... looking out across Poole Harbour to t...

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Need Syntax for "AND" to Evaluate 2 Cells
I need to evaluate 2 cells while inside an "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". I thought AND would work but I cannot get it to work; I receive a syntax error on the AND(Range... line. Can someone please provide me the proper syntax to evaluate the 2 cells? Here's my code... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveSheet.Name = "Sheet1" Then And(Range("I3") <> "", Range("K4") = "") Then Range("K4") = Range("K3") End...

Enter "1", cell show ".01". Why?
Any number typed into a cell is divided by 100. If proceded by "=" the number is correct. What caused this and how can I fix it? Try this .. Click Tools > Options > Edit tab Uncheck "Fixed decimal" > OK Things should be back to normal now .. (it's a fixed decimal setting !) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Yonian" <Yonian@discussions.microsoft.com> wrote in message news:40499CA4-7FAF-42A6-8B19-A90881735C50@microsoft.com... > Any number typed into a cell is divided by 100. > If p...

selecting a cell
I seem unable to select a single cell, or a single row--click on one in the normal manner, and the two below also highlight, then delete or whatever command is given. If I input a number/text, that just goes into the one cell. tapping F8 increases this to two wide and three high automatically selected. Also, very slow to do almost anything. Thanks Pat, Are you by any chance using Excel 2007? If so there is a known bug that causes multiple cell selection and I understand this has been reported to Microsoft. If you take the zoom level up and down this is reported to cl...

Currency Settings
I have created a report using VBA and Excel OLAP cube. Some of the metrics on the report are currency numbers and are to be reported in USD so I have formatted them accordingly using format ( e.g. "$ #,##0.00"). Some of the users in Germany who use decimal (.) as separator between numbers are not able to see the format correctly. I hope they use comma in place of decimal and decimal in place of commas as we normally do. Also other users in Europe see Pound or Euro sign instead of dollar. My report is being used in many geographies and I have to use $ which every body should be able t...

Setting Outlook settings to new server
We are in the process of moving mailboxes to a new server. Is there a utility or ad policy to automatically configure outlook to point to the new exchange server? Instead of manually configuring the users outlook profile. On Tue, 21 Nov 2006 11:15:01 -0800, Rich <Rich@discussions.microsoft.com> wrote: >We are in the process of moving mailboxes to a new server. Is there a utility >or ad policy to automatically configure outlook to point to the new exchange >server? Instead of manually configuring the users outlook profile. If you are moving the mailboxes in the same org vi...

how can i set this to save atomaticly
please tell me how to make this save my work automaticly. In Publisher 2000 you can have it "remind you to save" automatically. Tools, Options, User Assistance -- Don Vancouver, USA "MUCCA" <MUCCA@discussions.microsoft.com> wrote in message news:2BAE6F82-16EF-4F34-8866-9BE66333656A@microsoft.com... > please tell me how to make this save my work automaticly. MUCCA wrote: > please tell me how to make this save my > work automaticly. =================================== This? Are you referring to Microsoft Publisher? Which version? -- John Inzer retu...

cell will not center
Hi. I have a user with an Excel worksheet. There are multiple rows and columns and they are all set on center alignment, (center alignment icon on the toolbar as well as Format Cells --> Horizontal Alignment --> Center.) The alphabetical characters align correctly but the numerical don't, as they will only left align. Format Cells --> Number is set to General, so I don't know why it won't change the alignment. Other than the worksheet being corrupted, I don't know what could be wrong with it. Any suggestions are much appreciated. Thanks! Hilary =?Utf-8?B?SG...

Color not working
I am operating Windows 2000, using Office Pro 2003. I am no longer able to format my cells with colors, either shading or text coloring. I have run the repair feature, but it didn't work. Bold, Underline, and similar functions still work. Please help. Thanks. CC Have a look at this: Changes to Fill Color and Fill Pattern Are Not Displayed http://support.microsoft.com/?id=320531 Biff >-----Original Message----- >I am operating Windows 2000, using Office Pro 2003. I am >no longer able to format my cells with colors, either >shading or text coloring....

Count # of cells b/w cells ...
Hello, I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0 0 0 0 0 7 etc. The number of zero's between the 7's is random. I want a formula tha would count the number of zeros between the 7's. Thanks, Ari Bar -- AriBar ----------------------------------------------------------------------- AriBari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38806 Assume A5:A20 is the data, try this: B5 = A5+B4 (copy formula down) Now make a table with 2 column...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

Is it possible to set a slide transition that mimics turning page
I've been asked for help in setting up a PPT presentation to have slide transitions that mimic turning pages. I have seen this done using Keynote on a Mac but the software being used for this presentation is PowerPoint. Any ideas on if this is available or where I may find a download to add to my library? Thanks. MaryAnn PowerPoint 2007 The setting I use to achieve that is:- Animations tab / Transitions to This Slide group / Wipes / Strips Left-Up If my comments have helped please hit Yes. Thanks. "MeyerMA3" wrote: > I've been ask...

Paper settings change for specific printer
I just installed access 2007 and my application uses reports to print labels. Specifically a Dymo turbo 400 and 30252 address labels. when i was using acess 2003 i specified to use a specific printer and everything was great. and when i copied updated FrontEnds to my networked computers every thing transfered and the labels printed fine however with 07 the "Specifc printer" has a different page size.. i fixed all the reports on my master FE but the changes didnt stay when i copied FE to other computers and im not sure where to go. i installed all the printers from the s...

format cell #4
In Access, I can set up a field that "forces" the user to enter info - a date, for example - in a certain way, such as 25 Jan 05 or enter time as 12:15 AM. Is there a way that I can "force" this in excel? Thank you. Hello- Without invoking something more technical, you can select the cell(s) and go to Data>Validation and choose what type of entry be allowed in the field. Format the cell in the manner you wish to have the date or time expressed. HTH |:>) "HJC" wrote: > In Access, I can set up a field that "forces" the user to enter in...

Setting the Default font for Headers and footers in Excel 2002
I have a user who wants to know how to change the default font for headers/footers in Excel XP. I tried changing the font in each and saving the file as a template called 'Book' in the XLStart directory but that didn't work. Any ideas? She also wants to know how to set the default font for PowerPoint XP. Thanks! ...

Setting the default font and font size for Excel comments
Hi, Do you know how to set the default font and font size in Excel for the cell 'Comments'? I have looked in Options as well as in the registry to see if I can change the default from Tahoma (size 8) to another (i.e. Arial size 12). Your help would really be appreciated. Thanks, Clint - UK ...

Formatting cells and getting pound signs
I am using Excel 2003 with all updates as of 4/28/04 and trying to format a cell using the custom category and choosing the #,##0.00 type. I am trying to add the $ symbol at the beginning of the type and add text at the end of the type to look like this $#,##0.00 "text". When I do this however it shows up in my cell on my worksheet as ##########. It does know what the value is and shows as I would expect it to when I place mouse over cell in a balloon If I use only the $ symbol befor the type it shows fine. If I use only the "text" after the type is shows fine. Using the...

Can I set SQL mirroring across WAN?
We have a 100 Mb dedicated DR link to another data center. Want to set up a SQL 2008 database to use mirroring from primary to a standby server at the remote data center. I did a ping test and here is the result. ping -n 100 -l 5120 <target IP> Ping statistics for <target IP> Packets: Sent = 100, Received = 99, Lost = 1 (1% loss), Approximate round trip times in milli-seconds: Minimum = 8ms, Maximum = 19ms, Average = 11ms is the network latency good enough for database mirroring? Does anyone know the min. requirement? Field experience will be fine too....

Find and replace with bold in cells
I have a VB6 program that is executing Excel 2007, opening a worksheet, and extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b>" at the start of it and "</b>" at the end of it. How do I set this up in VB6? Thanks! The following function will return a string including <b> and </b> tags from the text of cell R. Function BoldMarkup(R As Range) As...

how to insert borders on flyer within Microsoft publisher?
Please help, I'm trying to insert a border around a flyer...I'm using Microsoft Publisher. Thanks, Harriet Is it a clipart border, Borderart or a simple rectangle? What problems are you having? What version Publisher? Any border you insert should be sent to the back so it does not interfere with your main design. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Harriet" <Harriet@discussions.microsoft.com> wrote in message news:7E2ED4D8-CC09-497F-A17C-44AE41F951C9@microsoft.com... > Please help, I'm ...

Conditional Formatting on cells beginning with a hyphen
Is it possible to do conditional formatting on cells beginning with a hyphen? Thanks, Greg 1. Place the cursor in A1 cell and select the Range 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and paste the below formula =LEFT(A1,1)="-" 4. Click Format Button>Font>Color select your desired font & Background Color pattern and then give ok Change the cell reference of A1 to your desired cell, if required. But keep in mind that when applying the conditional formatting the Active cell should be in the ce...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....