Changing font in Excel

[pblomfield]

This should be simple but I can't get it to work.

 

I'm processing rows in an Excel table. They are not always processed sequentially as sometimes rows have to be filtered out until data is corrected so, in order to see which rows have been processed once the filter is removed I would like to mark the processed rows in some way e.g change the text to italic or do something exotic like colour processed rows. Colouring the rows may be tricky as there are no keyboard shortcuts in Excel 2007 that I know of that will do that so for simplicity I tried the italics with, after selecting the row:

 

<TEXTTYPE:<CTRLD>I<CTRLU>>

 

It doesn't work. It does of course if I type it manually but in a macro it seems to be totally ignored. I've tried splitting the typing onto three textype commands with delays in between. I've slowed the type speed right down to ridiculous. I get the same result if i try to set text to bold. Anyone out there have any ideas?

[paul]

Have you tried the simpler <TEXTTYPE:<CTRL>I>?

[pblomfield]

Thanks for the suggestion, Paul. I had tried that but got the same result.

 

 

[paul]

Ah, you gave us all the clue, and none of us spotted it!

I think you'll find this works: <TEXTTYPE:<CTRL>i>

 

Use a lower-case i, not an upper-case I!

[pblomfield]

Thanks Paul, you've cracked it. I don't think I ever would have thought of that in a million years. If you manually type Ctrl-Shift-I it doesn't italicise but if you type Ctrl-I with Caps lock on it does. Much appreciated.

 

 

[paul]

Thanks Paul, you've cracked it. I don't think I ever would have thought of that in a million years. If you manually type Ctrl-Shift-I it doesn't italicise but if you type Ctrl-I with Caps lock on it does. Much appreciated.

Actually, if you think about it, it's not possible to type Ctrl-Shift-I unless you have Caps Lock down! Assuming we're not using Caps Lock, the only way you can get I instead of i is by pressing the Shift key. So really it's a choice between Ctrl-I and Ctrl-Shift-i, which is probably what the computer "sees". When you type the combination in, you're typing Ctrl-Shift-i; when you TextType Ctrl-Shift-I, you're not asking MEP to do the same thing.

[pblomfield]

The macro worked OK after your suggestion. Thanks again. However italicising the lines did not provide a complete solution. I found that though I could visually "filter" the rows that had been processed I couldn't filter them with Excel. Colouring the rows with ME mouse & keyboard commands would have been flakey so I wrote a simple Excel macro to colour the cells and tried launching the macro by typing the macro shortcut from ME. I thought there may be some reason why the ME macro would go wobbly - after colouring it goes on to do a heap of other stuff - but it worked a treat. That one could run Excel macros from ME is probably not a revelation to anyone who has used ME for more than 5 minutes but it was to me who has only been using ME for about 4 years - yes, I'm a bit slow at times. However, the combination of ME and Office macros opens up a big heap of possibilities which may be difficult with just one or the other.

[acantor]

Duplicate message!

[acantor]

I know a clunky way to modify the font colour in Excel:

 

1. Press "Ctrl + 1" (opens the "Format Cells" dialog box. That's a number one, not an L.)
   
2. Wait for the dialog box to open.
   
3. Press "N" to navigate to the "Number" tab.
   
4. Press "right arrow" twice to navigate to the "Font" tab.
   
5. Press "Alt + c" to navigate to the "Color" drop-down list.
   
6. Press a sequence of keys involving "Home," "End," and the four arrow keys to pick a colour.
   
7. Press either "Enter" or "space" to finalize your choice.
   
8. Press "Tab" to move the focus off the drop-down list.
   
9. Press "Enter" to do the equivalent of clicking on the "OK" button.
   

 

All of this is easily automated with Macro Express. My guess is the only place you would need to insert a time delay is at Step 2.

 

A more elegant way to do this would be to use VBA in Excel… But let's not go there!

[pblomfield]

Yuk! Thought it wouldn't be simple, that's why I avoided it. The Excel VBA to colour the row green ("Good") was one line - Selection.Style = "Good". No doubt there are similar one liners for other styles. Horses for courses.

 

 

[acantor]

Just for fun, I wrote the (clunky) script for the benefit of those who don't know VBA.

 

Text Type (Simulate Keystrokes): <CONTROL>1
Wait for Window Title: Format Cells
Text Type (Simulate Keystrokes): n
Text Type (Simulate Keystrokes): <ARROW RIGHT><ARROW RIGHT>
Text Type (Simulate Keystrokes): <ALT>c
Text Type (Simulate Keystrokes): <HOME>

// Start colour picker
// The next line of code moves focus to the desired colour swatch. Modify as needed.
// In Excel 2007, <ARROW DOWN> moves focus to a shade of red. 
// Add other arrow key commands to select a different colour.

Text Type (Simulate Keystrokes): <ARROW DOWN>

// End colour picker

Text Type (Simulate Keystrokes): <ENTER>
Text Type (Simulate Keystrokes): <TAB>
Text Type (Simulate Keystrokes): <ENTER>