Cell Formatting with a macro...

Related to a previous topic.

I have a worksheet that uses conditional formatting.  However, I need
to add a 4th condition.  I need  to code a macro that checks the
content of a cell, and then changes the formating (color) of a range
of cells if the condition is met.

For example if cell M1 is populated with an 'X', then I want excel to
change the color of the cells (pattern) for the range A1:M1.

Basically, the macro will check for values in the M column and
highlight all the cells in the intersecting row.

I used conditional formating to perform other tasks, but since Excel
only supports 3 conditions (4 if you count the initial/default
condition) now I need a macro!

Please help.

0
Moc835 (2)
5/29/2007 8:01:25 PM
excel 39879 articles. 2 followers. Follow

1 Replies
296 Views

Similar Articles

[PageSpeed] 18

Basic idea. Incorporate into a worksheet_change event for it to be 
automatic.

Sub selectcaseformatting()
Select Case ActiveCell.Value
Case 1: x = 3
Case 22: x = 5
Case 31: x = 6
Case 46: x = 8
Case Else
End Select
ActiveCell.Interior.ColorIndex = x
End Sub

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"MOC835" <Moc835@gmail.com> wrote in message 
news:1180468885.507481.294840@k79g2000hse.googlegroups.com...
> Related to a previous topic.
>
> I have a worksheet that uses conditional formatting.  However, I need
> to add a 4th condition.  I need  to code a macro that checks the
> content of a cell, and then changes the formating (color) of a range
> of cells if the condition is met.
>
> For example if cell M1 is populated with an 'X', then I want excel to
> change the color of the cells (pattern) for the range A1:M1.
>
> Basically, the macro will check for values in the M column and
> highlight all the cells in the intersecting row.
>
> I used conditional formating to perform other tasks, but since Excel
> only supports 3 conditions (4 if you count the initial/default
> condition) now I need a macro!
>
> Please help.
> 

0
dguillett1 (2487)
5/29/2007 10:29:57 PM
Reply:

Similar Artilces:

Slow VBA macro
Every time the following code runs and it reaches a blank cell it takes 2-3 seconds to update the cell with a zero: Do Until ActiveCell.Offset(1, -3) = "" ActiveCell.Offset(1, 0).Range("A1").Select Select Case ActiveCell Case Is = "" Selection.FormulaR1C1 = 0 End Select Loop I've tried Application.ScreenUpdating = False but the macro is still very slow. Any suggestions as to ho...

Word Doc Macro help needed please!
I need to make a mocro that will work in a word doc. This macro would start with [000001] and count up one number each paragraph. For example paragraph two would auto show [000002] and so on. Any help would be GREAT! I have a word file which contains 100 pages. All the pages have name & address of companies.(not in table) i have to transfer them to excel in column format(for example first name, last name, address, phone etc) can any one tell me code for this( i know how to open word & creat new excel sheet) . I need code for how to reach to lines of word. r there any thing like &quo...

autofit: cell height expands with text entered?
For a form: can a user enter mass quantities of text in a cell and have the cell depth expand so it fits? does Merging Cells limit this ability? I made a giant cell to handle the text the user might enter. I can't figure out where to set this... I have copied & pasted formatting from one worksheet to another without luck. Thanks. Sandy Merged cells don't adjust rowheight for wrapped text (like non-merged cells do). Jim Rech wrote a macro called AutoFitMergedCellRowHeight that you may like: http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05 Sandy wrote:...

Go To the next empty cell in Column A
Using Vista and Excel 2007, I will be constructing a mailing list with 10 columns. In the first empty row of column A will be added a new name for the list. With 10 columns it is not possible to view Column A from Column L on screen. With hundreds of names to add to the list, I need a fast way to go to the next empty cell in column A to add the next name.. I am familiar with tables in Access where there is an icon that will take me to the next empty cell in column A. Is there a similar one stroke command to take me to the next empty cell in column A from anywhere in an Excel ...

User defined functions aware of what cell they are placed in?
Hi, I would like to make a user defined function which needs to know in what cell and what worksheet it is placed in. I will be using this UDF in multiple cells on multiple worksheets. I originally just passed the cell row and column as parameters to the UDF however this ended up updating all worksheets and not just the one the UDF was on. Is there any way to do this? Option Explicit function myfunct(something as somethingelse) as something msgbox application.caller.address & vblf _ & application.caller.parent.name & vblf _ & application.caller.pare...

copy date in a cell if within a date range
Column M is a listing of percentages Column A is various dates, anywhere from Jan 1, 1998 to the present. I need to copy the contents of let's say M3 into cell T3 is the date in cell A3 is any date in the year 2010. If the date is in another year, leave cell T3 blank Thanks "carrerapaolo" wrote: > Column M is a listing of percentages > > Column A is various dates, anywhere from Jan 1, 1998 to the present. > > I need to copy the contents of let's say M3 into cell T3 is the date in cell > A3 is any date in the year 2010. If the ...

Excel-Multiple Cells Being Hi-lited
Sometimes when I'm setting up a worksheet and I left-click in a cell, multiple cells in the same column are hi-lited. After it happens the first time, it continues as I move through the worksheet, reducing my ability to get work done considerably. After some trial and error, it seems to occur when I've been adding and/or deleting columns and/or rows, after a header has been installed. I can move throughout the worksheet using the arrow keys, but it is a time consuming and cumbersome technique. I think the version I'm using is Office Professional 2007 (file extensi...

How do I convert time format to text?
I have a cell with a formula of =text(c3-b3,"h:mm") as the difference from cell b3 and c3. Assume the result is 5:00. I need to minus another cell value that is formatted as general text. eg. =text(c3-b3,"h:mm")-G3 Thanks....Andrew Hi Andrew- Try the VALUE() fx and format that cell with your choice of Time Formats. HTH |:>) "Andrew" wrote: > I have a cell with a formula of =text(c3-b3,"h:mm") as the difference from > cell b3 and c3. Assume the result is 5:00. I need to minus another cell value > that is formatted as general text. ...

Custom Formatting a Chart Data Label
I want to create a chart which will display a zero value on the data label where there is in fact a zero value, and show N/A on the data label where it didn't apply. The formula I am using in the spreadsheet uses a nested formula to make this determination: =IF(AND(B22+C22>0,C22>0),B22/C22,NA()) Presently, it works but displays the #N/A on the chart where I would prefer it simply displayed as N/A. Can I do this with a custom format on the data label?? Thanks so much! I don't know how you all do it but your amazing! -- If you can read this, thank a Teacher... If your read...

Go To an address specfied in a cell
Hello Folks, Does anyone know how I can move the cursor to a cell, the address of which is specified in another cell? Here is the scenario. I enter a list of hours worked in a specfic week on a data entry sheet. I hit a button and the values are copied to a data summary sheet, the position depends on the Week No., the first cell is specfied as the address "Data!29" for Week 5. I reckon I can handle a recorded macro to copy and paste the data but how do I locate the correct start cell? I have tried copying and pasting into the GoTo box but that doesn't work. Data!J29 Wee...

Formatting detail section w/two lines
HELP, I'm trying to create the below in a report, but when I align the "title" field below the "name" field and the "background" field is more than one line, the "title" is no longer directly underneath the name. Is there anyway to accomplish this? Name Background Title Thanks for your expertise! Sincerely, Cynteeuh A couple of things to try. 1- Size Backgroundbigger than two lines and set Can Shrik to No. 2- Combine Name and Title into a single text box and set Can Grow to Yes. =[Name] & Chr(13) & C...

Help with displaying the contents of the last populate cell.
I have numerous sheets within a book where all cells in column C in all sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you reference both columns P and R hold a monetary value and are formatted as Currency. Is there a way that cell D1 can automatically be populated with the contents of the last cell in column C that has a value in it. E.G. Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200. Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250. Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900. Any h...

Formula to process 3 cells using IF statements
I have 3 columns of experimental data (C:E). Row 30 contains the sums (C30:E30). I need a formula that will examine the three sums and return the column number that has the lowest sum. If more than one column is lowest, select one randomly. Example: C30 D30 E30 Result 10 11 12 1 (C) 22 20 21 2 (D) 32 31 30 3 (E) 40 41 40 Randomly select 1 or 3 51 50 50 Randomly select 2 or 3 60 60 60 Randonly select 1, 2, or 3 Can this be done with IF statements or do I need to write a macro? Well, this is a bit cumbersome, but it se...

Excel 97 Worksheet Protection and cell colour
Hi there, One of our users has setup a worksheet will a small range of cells that are locked (they have formulas in them), he then protects the sheet. He then wants to change the colour of some of the other cells, these cells are not locked, but he cannot change the colour of the cells. Is there an obvious solution? Cheers, Andy Hi AFAIK you can't do this in Excel 97 without first removing the protection -- Regards Frank Kabel Frankfurt, Germany andy wrote: > Hi there, > > One of our users has setup a worksheet will a small range of cells > that are locked (they h...

center text when conditional format applied
Textbox control on Access 2007 form lost its centered alignment when conditional formatting is applied. This did not happen in previous versions of Access. I tried using format painter to apply conditional formats from a control created in an earlier version, but this failed. The control created in the earlier version then lost it's centering after saving in Access 2007. -- Roy Handy Interesting. I tried to replicate this behaviour but failed, it all worked as it should. Try revisiting your form after applying the conditional formatting and resetting the text box to center text. ...

Enable macro #4
Is there a way that when a user opens an excel workbook that it will enable the macro without asking to disable or enable? I have users that click on disable, then the macro will not run. I have two macro buttons set up and they choose the one that is needed at this time. I would rather it open and the enable already be set without them having to click on it. Is this possible? No, Pam. Sorry. You can "force" them to enable. http://vbaexpress.com/kb/getarticle.php?kb_id=578 So, you could set it up that if they don't enable, they don't see... if they do enable, no...

Pointing to correct macro path using excel custom toolbar
I have created an excel 2000 template (.xlt) containing a number o macros. When I open copies of this template on various pcs, the macro function correctly, except I cannot successfully run the macros usin the custom toolbar I created, because (I think) within the toolbar th paths to the macros are pointed to the original location on my pc. An advice on how I can resolve this would be gratefully received -- Message posted from http://www.ExcelForum.com Have you thought about building the toolbar when the file opens? Or maybe separating the worksheet portion of the template from the code pa...

Coloring the Desired cells
Hello, I have a work sheet in which i have to look for word "Test" and color the rows below it. There are different words like "Test 1" "Test 2" and each set needs a different color. Can I get some help with the macro for it? eg: Test1 row 1 row 2 Test 2 row 1 row 2 the number of rows in each group is not constanr. Thank you, Harsh Excel will need to know the logic of the rows and colors to be able to determine how many rows to color. You say the number of rows is not constant, but obviously you know how many rows to color. How do you know t...

excel locks up after selecting a cell #2
excel locks up after selecting a cell. When ever, I select a Cell, that will automatically selects all the cell and this freezes the entire computer. Can any body who would help me resolve this issue? Please help.... ...

Do a calculation in cells with text data format
I have a few columns of cells having a mixed data format of number and text. Is it possible to convert the first row of numbers in text data format for further calculation? Your guidance to accomplish it is appreciated. Thanks, Ray Example? -- Regards, Peo Sjoblom "Ray" <NoSpam-ZQLi@GMail.com> wrote in message news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl... > I have a few columns of cells having a mixed data format of number and text. > Is it possible to convert the first row of numbers in text data format for > further calculation? Your guidance to accomplis...

Macro for Internet Order Window
I want to create a macro to launch the CTL-SHIFT-F10 "Internet order" button (because I am going to make a custom button for it). However, I cannot record the macro because as soon as I hit CTRL-SHIFT-F10, the order popup window pops up and I cannot end the macro by doing SHIFT-F3. If I close the window, then hit SHIFT-F3 to end the macro, the macro just opens and then closes the internet order window. Is there another way to do it? Thanks, mickie No ideas? No one has done this? Mickie wrote: > I want to create a macro to launch the CTL-SHIFT-F10 "Int...

Changing of Cell protections after saving Excel File (2002)
This problem occurs when I protect a document using a macro 4.0 function: =PROTECT.DOCUMENT(TRUE,,,TRUE,TRUE). When I use the function within a macro4.0 macro, on an original file, everything works fine. The sheet has unlocked cells, and when the sheet is protected, it allows me to access those cells. But if I save the file, or save.as another name, then the fun begins. The enable selection of the sheet( view codes) has gone from 0-xlNoRestrictions to -4142- xlNoSelection. This locks me out of doing anything in the sheet. When I unprotect and then re-protect the sheet using the T...

how do I change cell references automatically in formulas
In Excel 2000, I have data in 80 rows and 10 columns. Each week I add a new row. I have a separate chart for each column with the data range from the first row to the last.. Each week I have to change the data range to reflect the new last row for each chart. Is there someway I can do this automatically? http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "jnw3" <jnw3@discussions.microsoft.com> wrote in message news:8A3551F9-FBC6-4841-95B4-618AB1893190@micros...

Cell can left indent; anything for right side of cell?
Is there a way to leave more space between the end of a word and the right side of the column? I indented the left side of the column by using the indent option set to "1" in the alignment tab of the formatting box. Besides adding an extra column to the end of the row and making it very small in width and extending the border to that extra "spacer" column, is there a way to make this space? My spreadsheet is done and if I have to do this, I'll have tons of work re-writing code. I was going to use Word for the final presentation of the data, but it didn't work ou...

Help with macro/and or formula?
Perhaps this is a silly question, but i just can't figure out how t format this worksheet. The problem is that a lot of the cells hav numbers in them with negative signs in the back instead of in the fron (ie. 23-) and i was wondering if anyone knew of a macro/formula i coul use to quickly fix this problem for all the cells. I would b extremely grateful. Thanks -- brefed1 ----------------------------------------------------------------------- brefed15's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3557 View this thread: http://www.excelforum.com/showthre...