Apply format to >0 cells

Hi All
I have a code below that formats cells - but it formats all cells in the row 
& I need it to only format cells >0 in the row.
I tried a few things and got errors .... How do I incorporate the additional 
If Then Else into this code?

Sub Decorate()
Dim rngB As Range
Dim rngTemp As Range
Set rngB = Sheets("Sheet2").UsedRange.Columns("A:S")
For R = 1 To rngB.Rows.Count
If Sheets("Sheet2").Cells(R, 1).Value = Range("A3") Then
Set rngTemp = Sheets("Sheet2").Cells(R, 1).Range("C1:S1")

'''' If rngTemp.Value <1 Then   (I tried adding this - but got a "type 
mismatch" error???)
'''  Else

rngTemp.Font.Bold = True
With rngTemp.Interior
..ColorIndex = 41
..Pattern = xlSolid
..PatternColorIndex = xlAutomatic
End With
'''' End If
End If
Next R
End Sub
-- 
Thank for your help
BeSmart
0
Utf
3/6/2010 3:43:23 AM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
664 Views

Similar Articles

[PageSpeed] 52

Firstly note that a space and underscore at the end of a line is a line break 
in an otherwise single line of code.

I have had to guess a little to correct some of your code. The following 
line does not say which sheet  Range("A3") belongs to.

If Sheets("Sheet2").Cells(R, 1).Value = Range("A3") Then

I changed it to the following. Edit to the correct sheet if required.

If Sheets("Sheet2").Cells(R, 1).Value _
    = Sheets("Sheet2").Range("A3") Then

I had difficulty working out what range you wanted in the following line.

Set rngTemp = Sheets("Sheet2").Cells(R, 1).Range("C1:S1")

I changed it to the following.

With Sheets("Sheet2")
      Set rngTemp = .Range(.Cells(R, 1), .Cells(R, "S"))
End With

Revamped code as follows but I am really not sure that my assumptions above 
are correct for what you want so feel free to get back to me.

Sub Decorate()
Dim rngB As Range
Dim rngTemp As Range
Dim c As Range
Dim R As Long

Set rngB = Sheets("Sheet2").UsedRange.Columns("A:S")
For R = 1 To rngB.Rows.Count
  If Sheets("Sheet2").Cells(R, 1).Value _
    = Sheets("Sheet2").Range("A3") Then
    
    With Sheets("Sheet2")
      Set rngTemp = .Range(.Cells(R, 1), .Cells(R, "S"))
    End With
    
    For Each c In rngTemp
      If c.Value < 1 Then
        c.Font.Bold = True
        With c.Interior
          .ColorIndex = 41
          .Pattern = xlSolid
          .PatternColorIndex = xlAutomatic
        End With
      End If
    Next c
  End If
Next R
End Sub

-- 
Regards,

OssieMac


"BeSmart" wrote:

> Hi All
> I have a code below that formats cells - but it formats all cells in the row 
> & I need it to only format cells >0 in the row.
> I tried a few things and got errors .... How do I incorporate the additional 
> If Then Else into this code?
> 
> Sub Decorate()
> Dim rngB As Range
> Dim rngTemp As Range
> Set rngB = Sheets("Sheet2").UsedRange.Columns("A:S")
> For R = 1 To rngB.Rows.Count
> If Sheets("Sheet2").Cells(R, 1).Value = Range("A3") Then
> Set rngTemp = Sheets("Sheet2").Cells(R, 1).Range("C1:S1")
> 
> '''' If rngTemp.Value <1 Then   (I tried adding this - but got a "type 
> mismatch" error???)
> '''  Else
> 
> rngTemp.Font.Bold = True
> With rngTemp.Interior
> .ColorIndex = 41
> .Pattern = xlSolid
> .PatternColorIndex = xlAutomatic
> End With
> '''' End If
> End If
> Next R
> End Sub
> -- 
> Thank for your help
> BeSmart
0
Utf
3/6/2010 4:50:29 AM
Why not just use conditional formatting?  Here's code that would allow you 
define a range by changing a few variables in it.

See if this works for you...

Sub Decorate()
'set cells in columns C:S that have a value
' Greater Than 0 to .ColorIndex 41, solid
  Const firstRowToFormat = 2
  Const firstColToFormat = "C"
  Const lastColToFormat = "S"
  Dim lastRowToFormat As Long
  Dim tmpString As String
  Dim rngB As Range
  
  tmpString = Worksheets("Sheet2").UsedRange.Address
  lastRowToFormat = _
   Range(Right(tmpString, Len(tmpString) - InStr(tmpString, ":"))).Row
  
  Set rngB = Worksheets("Sheet2"). _
   Range(firstColToFormat & firstRowToFormat & ":" & _
   lastColToFormat & lastRowToFormat)

'for Operator, use xlLess if you need Less Than condition
  With rngB
    .FormatConditions.Delete
    .FormatConditions.Add _
     Type:=xlCellValue, _
     Operator:=xlGreater, _
     Formula1:="0"
    .FormatConditions(1).Font.Bold = True
    .FormatConditions(1).Interior.ColorIndex = 41
    .FormatConditions(1).Interior.PatternColorIndex = _
     xlAutomatic
  End With
  Set rngB = Nothing
End Sub


"BeSmart" wrote:

> Hi All
> I have a code below that formats cells - but it formats all cells in the row 
> & I need it to only format cells >0 in the row.
> I tried a few things and got errors .... How do I incorporate the additional 
> If Then Else into this code?
> 
> Sub Decorate()
> Dim rngB As Range
> Dim rngTemp As Range
> Set rngB = Sheets("Sheet2").UsedRange.Columns("A:S")
> For R = 1 To rngB.Rows.Count
> If Sheets("Sheet2").Cells(R, 1).Value = Range("A3") Then
> Set rngTemp = Sheets("Sheet2").Cells(R, 1).Range("C1:S1")
> 
> '''' If rngTemp.Value <1 Then   (I tried adding this - but got a "type 
> mismatch" error???)
> '''  Else
> 
> rngTemp.Font.Bold = True
> With rngTemp.Interior
> .ColorIndex = 41
> .Pattern = xlSolid
> .PatternColorIndex = xlAutomatic
> End With
> '''' End If
> End If
> Next R
> End Sub
> -- 
> Thank for your help
> BeSmart
0
Utf
3/6/2010 5:05:24 AM
Hi JLatham

That works great, but it formats all rows - I need it to only format rows 
where the string in column A matches to the value in cell A3.  If it matches 
then the formatting happens to that row.

I'll then repeat the macro but for A4, A5, A6 (and applying a different 
interior colour) - unless you know of how to do that automatically in the 
macro??

-- 
Thank for your help
BeSmart


"JLatham" wrote:

> Why not just use conditional formatting?  Here's code that would allow you 
> define a range by changing a few variables in it.
> 
> See if this works for you...
> 
> Sub Decorate()
> 'set cells in columns C:S that have a value
> ' Greater Than 0 to .ColorIndex 41, solid
>   Const firstRowToFormat = 2
>   Const firstColToFormat = "C"
>   Const lastColToFormat = "S"
>   Dim lastRowToFormat As Long
>   Dim tmpString As String
>   Dim rngB As Range
>   
>   tmpString = Worksheets("Sheet2").UsedRange.Address
>   lastRowToFormat = _
>    Range(Right(tmpString, Len(tmpString) - InStr(tmpString, ":"))).Row
>   
>   Set rngB = Worksheets("Sheet2"). _
>    Range(firstColToFormat & firstRowToFormat & ":" & _
>    lastColToFormat & lastRowToFormat)
> 
> 'for Operator, use xlLess if you need Less Than condition
>   With rngB
>     .FormatConditions.Delete
>     .FormatConditions.Add _
>      Type:=xlCellValue, _
>      Operator:=xlGreater, _
>      Formula1:="0"
>     .FormatConditions(1).Font.Bold = True
>     .FormatConditions(1).Interior.ColorIndex = 41
>     .FormatConditions(1).Interior.PatternColorIndex = _
>      xlAutomatic
>   End With
>   Set rngB = Nothing
> End Sub
> 
> 
> "BeSmart" wrote:
> 
> > Hi All
> > I have a code below that formats cells - but it formats all cells in the row 
> > & I need it to only format cells >0 in the row.
> > I tried a few things and got errors .... How do I incorporate the additional 
> > If Then Else into this code?
> > 
> > Sub Decorate()
> > Dim rngB As Range
> > Dim rngTemp As Range
> > Set rngB = Sheets("Sheet2").UsedRange.Columns("A:S")
> > For R = 1 To rngB.Rows.Count
> > If Sheets("Sheet2").Cells(R, 1).Value = Range("A3") Then
> > Set rngTemp = Sheets("Sheet2").Cells(R, 1).Range("C1:S1")
> > 
> > '''' If rngTemp.Value <1 Then   (I tried adding this - but got a "type 
> > mismatch" error???)
> > '''  Else
> > 
> > rngTemp.Font.Bold = True
> > With rngTemp.Interior
> > .ColorIndex = 41
> > .Pattern = xlSolid
> > .PatternColorIndex = xlAutomatic
> > End With
> > '''' End If
> > End If
> > Next R
> > End Sub
> > -- 
> > Thank for your help
> > BeSmart
0
Utf
3/6/2010 9:56:01 AM
Hi OssieMac

I did one quick tweak and it worked great!!!! Thank you so much!! - your 
assumptions were correct & I'm sorry for not providing better information...

i.e. changed:
If c.Value < 1 Then
to
If c.Value > "0" Then


Can I ask one last question please...

Is there a way to apply the interior colour that is nominated by the user? 
i.e. they apply fill to B3 - next to A3?? so we end up with a list of product 
names in A3:A10 and fill colours to apply to each product name in B3:B10.

I have 7 products to apply it too (at the moment) - do I need to create 7 
macros or is there a way of repeating the macro for cell A4, then cell A5, 
then cell A6 etc...

I would finish with a table of data where each row is colour coded to match 
the list of 7 products at the top of the page.

If you can help me with this it would be amazing.
Thanks again
BeSmart

0
Utf
3/6/2010 10:16:01 AM
Hi again,

I am still not sure that I fully understand. You say to match colors B3:B10 
and then quote "list of 7 products at the top of the page".  B3:B10 is 8 
cells not 7.

My previous code started at the top of the page for setting the colors. 
However your quote "I would finish with a table of data where each row is 
colour coded to match the list of 7 products at the top of the page". I am 
wondering if R should start at row 11 under the products at the top of the 
page and not start from row 1.

If I understand correctly, the user will manually color the cells B3:B10 and 
you want to get these colors for each of the matches in cells A3:A10. If 
correct, then try the following. (I have started R at row 11)

Sub Decorate()
Dim rngB As Range
Dim rngTemp As Range
Dim c As Range
Dim R As Long
Dim colIdx As Integer
Dim j As Long

Set rngB = Sheets("Sheet2") _
    .UsedRange.Columns("A:S")

For j = 3 To 10
  For R = 11 To rngB.Rows.Count
    If Sheets("Sheet2").Cells(R, 1).Value _
      = Sheets("Sheet2").Cells(j, "A") Then
      
      colIdx = Sheets("Sheet2") _
        .Cells(j, "B").Interior.ColorIndex
      
      With Sheets("Sheet2")
        Set rngTemp = .Range(.Cells(R, 1), _
            .Cells(R, "S"))
      End With
      
      For Each c In rngTemp
        If c.Value > 0 Then
          c.Font.Bold = True
          With c.Interior
            .ColorIndex = colIdx
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
          End With
        End If
      Next c
    End If
  Next R
Next j
End Sub

'***********************************

Not sure if the following will help but have included it for info because 
the ColorIndex matrix provided in Excel help is not accurate.

The code will create a sample of all of the ColorIndex colors. The row 
number will be the ColorIndex. Run it on a blank worksheet and you can 
actually copy the various cells and use Paste Special -> Formats to put the 
colors in your range B3:B10.

Sub IntColIdx()
Dim i As Integer
'Edit "Sheet3" to match your required sheet.
With Sheets("Sheet3")
  For i = 1 To 56
    .Cells(i, 1).Interior.ColorIndex = i
  Next i
End With
End Sub

-- 
Regards,

OssieMac


0
Utf
3/6/2010 11:51:01 AM
Hi OssieMac

THAT is awesome!!!! Again with a few little changes it's working perfectly!!!!
Your assumptions were spot on!!!
Thank you soooo much!!!
-- 
Cheers
BeSmart


0
Utf
3/6/2010 2:51:01 PM
Reply:

Similar Artilces:

pivot table formatting #3
Using 2003 (11.5612.8107) This is my first real work with a pivot table. In 10 minutes I was able to create a beautiful table with exactly the desired look. However if there is any slight change, I have to rebuild things from scratch or at least reformat it. For example in the left most (Row field) i use Request Type. If I change the Request Types being displayed, the format of the table comes undone. I have read an old post and selected Pivot Table, Table options, Preserve Formatting. I've also clicked on Select, Enable selection. Yet if I add or remove one of the items to ...

How do I apply a combo box to multiple cells in Excel so that it .
I would like to apply a combo drop down box in Excel. However I only want the box to appear when the cursor is over the cell can anyone offer me some help on this matter? Thank you, Dwain Hi Dwain, Perhaps you are looking for Data Validation, available on the Data menu. See Debra Dalgleish's tutorial at: http://www.contextures.com/xlDataVal01.html --- Regards, Norman "DB" <DB@discussions.microsoft.com> wrote in message news:6939ADC2-CF1C-46AA-B163-BEE27E23356A@microsoft.com... >I would like to apply a combo drop down box in Excel. However I only w...

Excel 2007 Cell Style Question
Greetings, I just got upgraded to Excel 2007. I have made several custom cell styles that I use very frequently. I followed the instructions to create a Book.xltx in my XLStart foder. However, when I start Excel, the custom cell styles don't show up. How do I make this happen? TIA for your help! Ray ...

Conditional Format Dates
How do I conditionally format dates that are more than one year old? -- -Andy On Thu, 17 Jan 2008 08:12:06 -0800, Andy12 wrote: > How do I conditionally format dates that are more than one year old? In form design view, select the date control. Click on Format + Conditional Formatting. Set Condition1 to Expression Is In the next dialog box, write: [ControlName]< DateAdd("yyyy",-1,Date()) Change [ControlName] to whatever the actual name of your control is. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail On Thu, 17 Jan 2008 08:12:06 -0800,...

Apply view to all folders
Greetings, A user has numerous folders created under her Inbox in Outlook 2000. She woule like to create a custom view and then apply it to all folders automatically. I can create a custom view easily, but can't find a way to apply it to all folders other than on a one-by-one basis. Is there a way to apply the view to all folders at one time? Thanks much, Justin You can't apply it to all at once - unless you customize the Messages view. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Gu...

format a cell as running time
Hello All, I have a simple question I cannot figure out. I would like to keep a spreadsheet of my workouts(running) . I have columns "Total Time" and Split Time(s)" (multiple of these) How would I format a cell as running time? i.e. 22.20.93 and say one of the splits is 2.28.03 (two minutes etc...) Thanks for any suggestions, Homer One way: Format/Cell/Number/Custom mm:ss.00 then enter 2:28.03 In article <94D844C9-A8C9-42FC-91DF-BCDD252ACC6E@microsoft.com>, "Homer" <Homer@discussions.microsoft.com> wrote: > Hello All, > I hav...

Managing users' CRM 4.0 for Outlook settings
Is there a way to centrally manage users' CRM 4.0 for Outlook settings? (Perhaps via AD group policy, for example) By "settings" I mean all those settings in the users' personal CRM 4.0 for Outlook options: eg when synchronization occurs, what gets synchronized, local data groups etc...; Thanks! I have the same question, is there no ADM template for CRM? "Peter Lynch" wrote: > Is there a way to centrally manage users' CRM 4.0 for Outlook settings? > (Perhaps via AD group policy, for example) > > By "settings" I mean all tho...

GP 9.0 - Analytical Accounting Options - Post through to GL
Hello, One of the options in the Analytical Accounting Options window (Tools >> Setup >> Company >> Analytical Accounting >> Options) is 'Post through to GL for Trx posting'. I'm not clear of the purpose of this option. 1. Does this override the checkbox 'Post to GL' in the Posting setup? 2. To what transactions this option apply? Thanks in advance...Murali ...

Conditional formatting
I've followed the correspondence on conditional formatting on charts with interest but is it possible to conditionally format a line chart i.e. green above a certain value, red below that value? -- Brisbane Rob ------------------------------------------------------------------------ Brisbane Rob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25096 View this thread: http://www.excelforum.com/showthread.php?threadid=529599 Hi, Yes it's possible. http://www.andypope.info/charts/conditionalline.htm Cheers Andy Brisbane Rob wrote: > I've follow...

Printing Formatted Cells
I have conditional format on a cell to format with say Fill Red ... It displays OK on the screen but doesn't appear when printed? I'm sure it'll be something obvious! Is your print setting for color, or B&W? Long shot, but I'm not sure of any other reason why what-you-see is not what-you-get. -- Best Regards, Luke M "Charlie" <Charlie@discussions.microsoft.com> wrote in message news:C94E101A-9579-4FDB-8AEF-91D59E1EC3A4@microsoft.com... >I have conditional format on a cell to format with say Fill Red ... It > displays OK on the sc...

MS Excel does not interpolate empty cells
Trying to plot a contour map of a film thickness. The substarte is round. X/Y coordinates are not in a grid so the data table has several empty cells. Trying to use 'Tool/Options...', 'Chart' tab and selecting the 'Interpolate' radio button does nothing. The chart treats the empty cells as zero value. If it would interpolate I would be happy. I also tried this with a grid (square) X/Y table with a couple of empty cells and again, it did not interploate. If the cell contains a formula like =IF(A1=0,"",A1), then that cell is neither empty nor blank. I...

view applied?
What does the following WLM beta message mean? View applied not connected to imap4.xxxxxx.co.uk I had already clicked on this account and a couple of emails were downloaded. ...

Chart formatting problems
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Running Office 2008 on Mac OS X 10.5 Leopard <br><br>I am having problems with chart formatting (specifically formatting data labels). I've made all the changes I want and I can save the file, yet when I close it down and reopen it, the changes are lost. For example, I've changed the font type, size and color of my data labels and have rotated them 90 degrees counterclockwise but even after saving my changes, the document reverts to Excel's standard font and rotates my data labels horizontally...

Cells: Any way to code an event behind one?
I'm thinking I'd like to automatically select the next cell in a series after the user pastes something into the current cell. Can I write event code for a given cell, like "Cell(2,3).AfterUpdate()"? -- PeteCresswell It should be easy with a change event, but what does ... next cell in a series ... mean? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "(PeteCresswell)" <x@y.Invalid> wrote in message news:k8qln25tn2el46epqjf4r6nkld7l5oaa83@4ax.com... > I'm thinking I'd like to automatically select the next cell in a series >...

Why is Excel amending formatting itself, when i sort a sheet?
I have a fairly large spreadsheet that i need to sort regularly. Lately, Excel is changing the row & column formatting when i do this. I have version 2007, but it is saved as version 1997 - 2003 due to compatibility reasons. Can someone help resolve this issue please? I would need more information to be able to assist. When you say Row and Column formatting are you referring to the Width/Height of a column/Row? If cells are merged (joined) you may experience some difficulties with this when sorting. Also it may be something with word wrapping. Can you tell me more...

How do I split a cell in a table
How do I split a cell horizonally in a table in MW Pub 2007? the option is grayed out. thanks for your help -- Suegb Inc. You can't unless you merged the cells at one time and want to re-split them. You can create a text box(es) to mimic a split cell. -- Mary Sauer http://msauer.mvps.org/ "suegb" <suegb@discussions.microsoft.com> wrote in message news:946E2907-2C47-4A5A-B26F-4E951E3D25B5@microsoft.com... > How do I split a cell horizonally in a table in MW Pub 2007? the option is > grayed out. thanks for your help > -- > Suegb Inc. ...

How to turn off auto-formating of numbers into scientific number format.
This has to be one of the most annoying things I have ever seen come out of the Redmond collective. If you import numbers into an excel 2003 spreadsheet, like 2003E152, it will automatically format it in scientific notation. I have read http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q214233 I refuse to accept this as a "feature". I would like to know how to turn it off completely, so it never does the conversion. Thanks, twf Hi Can't be done in general. You have to do adjust the way you "import numbers into". HTH. Best wishes Harald "Tom Fe...

I've applied a Fill and now can't see the gridlines....
Does anyone know how to show the Gridlines through a fill? I've searched and searched but can't find the answer, yet the silly thing is I know it can be done as I have done it before (by accident) Any help mostgratefully received! bestregards, Tim Apply borders... In article <62E1EA83-287A-4FE3-BEC6-7C0155EA1850@microsoft.com>, "FizzyBunghole" <FizzyBunghole@discussions.microsoft.com> wrote: > Does anyone know how to show the Gridlines through a fill? I've searched and > searched but can't find the answer, yet the silly thing is I know it c...

Installing .net 2.0 on with Windows Sharepoint Services spk 2
Hi all, I have WSS with SP2 running on server 2003 with SQL 2000 Enterprise. All is running fine. I am planning on adding .net 2.0 and possibly sql 2005 express to the server. I plan on keeping the WSS site on .net 1.1. My question is when I do the ugprade is there anything o the WSS site or administration that I have to do after I have have installed the .net 2.0 framework? I just want to make sure I do not break anything or mess up any rights I have setup on the site. Thanks a bunch for any advise. Yes I will be making an image of the server before upgrading. -- Flanman On Fri,...

Conditional Formatting by Date
Howdy All, I have a budget workbook which contains a worksheet for every month and I need to format certain cells to show a value from a specific date forward. Example: Car payment needs to be applied on the 15th and remain there when the month rolls over. So on the fifteenth of May, I need cell C5 to display $350.00, but I don't want May's C5 to reset back to 0 when the date rollovers over to June 1st. I hope I am clear on this. Thanks for you time and attention, Brian hi! in A1: =TODAY() and in C5: =(IF(AND(MONTH(A1)=5,DAY(A1)=15),TEXT(350,"$000"),"&q...

M2005 Cannot Apply Epayment To Invoice
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C4AD73.81F30C10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I create invoices for payments from customers. I have those = automatically charged to their bank accounts and credited to my bank = account. When the payments hit my bank account and are downloaded into = Money, I go into the transaction and enter "Payment For Invoice" and = then try to go to the apply payment to an invoice screen but a nice = little pop up comes up in Money 2005 that says &qu...

today() conditional formatting
i have conditional formatting set for projects that are still pending so that formula is =today( formatted font is red, bol once the project is complete, the date is manually entered into the cell (changing it from the formula =today() to an actual date (i.e.: 4/19/04)) the problem is that the conditional formatting at that point still reads the date in that cell as today's date (even though it's no longer a formula), and continues to show as red, bold font... which indicates that the project is still pending is there a way to get around that hope that makes sense tia jill You...

Cross Apply problem
How do I get the desired result for the following: SOURCE TABLE: USSoilLyr FIELDS: TAXOR PARTSIZE PCT_R Alfisols ashy 60 Alfisols ashy 57 Alfisols ashy 38 Alfisols clayey 85 Alfisols clayey 79 Andisols medial 50 Andisols medial 36 DESIRED RESULT (TARGET TABLE): T_Order FIELDS: TAXOR PARTSIZE PCT_R Alfisols ashy AVG(60+57+38) Alfisols clayey Avg(85+79) Andisols medial Avg(50+36) My T-SQL Code, as follows, returns wrong calculations when I manually check the results. UPDATE T_Order SET ...

GP 9.0 move to new server
Currently we have GP 9.0 server software running on one server and SQL 2000 64 bit running on another. We need to load just the GP server software portion to another server, and leave the SQL database where its at on its current server. Can anyone point to a KB article, documentation or perhaps explain whats necessary to accomplish just a GP server software move or reinstall? Scott, If you are leaving SQL and the databases on the same server as they are now, all you need to do is reload Dynamics on the new server. Be sure you select a server client install during the process. Then...

Format Painter shortcut?
Is there a built-in keyboard shortcut for the Format Painter? If not, is there a way to to assign to some key combination? I know I can assign a key combination to a macro, but I would prefer not to write a macro just to get a keyboard shortcut for a standard function. Thanks -- Running Excel 2000 SP-3 on Windows 2000 i don't know of any but you could copy the cell with the correct paste special>formats and then use F4 to repeat the action in each cell you want to format "LurfysMa" wrote: > Is there a built-in keyboard shortcut for the Format Painter? > > If...