writing macro to gray out cells automatically

[Guest]

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

 

[Tom Ogilvy]

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

 

[Sharad Naik]

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

 

[Chip]

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.

 

[Myrna Larson]

Is there some reason you need to write a macro for this rather than using
conditional formatting?