Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing macro to gray out cells automatically
I need to write a macro that will do the following
If there is a check-mark symbol in a given cell on a row of data, all cells preceeding the check-mark cell will be grayed out. If the check-mark symbol is problematic, I can possibly pursuade my coworker to enter a certain alphanumeric character in the cell instead. For example: Row 9, column H has the checkmark or other "flag" Column a - g would be grayed out on that line only when the macro is run |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing macro to gray out cells automatically
A checkmark is not a standard character in most fonts. How would you define
your checkmark. anyway assume "A" is the trigger for each cell in range(cells(activeCell.row,1), _ Cells(activeCell.row,256).End(xltoLeft)) if cell.Value = "A" then range(cells(Cell.row,1), Cell).Interior _ .ColorIndex = 15 exit for end if Next -- Regards, Tom Ogilvy "CLRankin" wrote in message ... I need to write a macro that will do the following If there is a check-mark symbol in a given cell on a row of data, all cells preceeding the check-mark cell will be grayed out. If the check-mark symbol is problematic, I can possibly pursuade my coworker to enter a certain alphanumeric character in the cell instead. For example: Row 9, column H has the checkmark or other "flag" Column a - g would be grayed out on that line only when the macro is run |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing macro to gray out cells automatically
OK, ask your coworkers to enter say "gr123"
Even if thousands of cells to grey out below code will work in few seconds. Sub GreyOut() Dim c, gRoRange As Range, uRange As Range Set uRange = Sheet1.UsedRange With uRange Set gRoRange = .Cells(.Rows.Count + 1, 1) For Each c In .Cells With Sheet1 If c.Value = "gr123" And Not c.Column = 1 Then Set gRoRange = Union(gRoRange, .Range(.Cells _ (c.Row, c.Offset(0, -1).Column), .Cells(c.Row, 1))) End If End With Next c gRoRange.Interior.Color = 9868950 .Cells(.Rows.Count + 1, 1).EntireRow.Delete End With End Sub Sharad "CLRankin" wrote in message ... I need to write a macro that will do the following If there is a check-mark symbol in a given cell on a row of data, all cells preceeding the check-mark cell will be grayed out. If the check-mark symbol is problematic, I can possibly pursuade my coworker to enter a certain alphanumeric character in the cell instead. For example: Row 9, column H has the checkmark or other "flag" Column a - g would be grayed out on that line only when the macro is run |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing macro to gray out cells automatically
Well, if you are talking about using a form to make the checkboxs (not
a text symbol like Tom was assuming), you can right click on the checkbox, and have the check box linked to a cell...that way when it is clicked the linked cell returns the word "TRUE"....so just replace then in Tom's code the letter A with the word TRUE. Then you can just hide the row with the linked cells. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing macro to gray out cells automatically
Is there some reason you need to write a macro for this rather than using
conditional formatting? On Thu, 13 Jan 2005 08:21:02 -0800, "CLRankin" wrote: I need to write a macro that will do the following If there is a check-mark symbol in a given cell on a row of data, all cells preceeding the check-mark cell will be grayed out. If the check-mark symbol is problematic, I can possibly pursuade my coworker to enter a certain alphanumeric character in the cell instead. For example: Row 9, column H has the checkmark or other "flag" Column a - g would be grayed out on that line only when the macro is run |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically gray out rows when a particular field is populated | Excel Discussion (Misc queries) | |||
Automatically gray out a row when a particular field is populated | Excel Discussion (Misc queries) | |||
writing a macro that automatically opens another file | Excel Discussion (Misc queries) | |||
Automatically have even numbered rows shaded gray? | Charts and Charting in Excel | |||
Writing a Macro that will Plus Cells | Excel Programming |