Macro, if then next
 

I am trying to get the cells in one column to show highlighted given the
value in the adjacent cell in the next column =1, if not then no format
change is needed. The macro I have pieced together is listed below but it It
works for one row and then does not drop down to the next row.


Public Sub Highlight()
For Each cell In Selection
If cell.Offset(0, 1) = 1 Then
ActiveCell.Select
Selection.Interior.ColorIndex = 6#
Pattern = xlSolid
End If
Next
End Sub

Macro, if then next
 

"aseret" wrote in message
...
: I am trying to get the cells in one column to show highlighted given the
: value in the adjacent cell in the next column =1, if not then no format
: change is needed. The macro I have pieced together is listed below but it
It
: works for one row and then does not drop down to the next row.
:
:
: Public Sub Highlight()
: For Each cell In Selection
: If cell.Offset(0, 1) = 1 Then
: ActiveCell.Select
: Selection.Interior.ColorIndex = 6#
: Pattern = xlSolid
: End If
: Next
: End Sub

looks like you are using activecell.select when you should be using cell as
referenced in your loop. Try this

Public Sub Highlight()
Dim myCell As Range
For Each myCell In Selection
If myCell.Offset(0, 1) = 1 Then
myCell.Interior.ColorIndex = 6#
myCell.Interior.Pattern = xlSolid
End If
Next myCell
End Sub

Paul D

Macro, if then next
 

Hi,
Excel has a ConditionalFormating featu
Assuming your range is A1:A10 and must be Dynamically formatted based on the
values of B1:B10 (special format when B is 1).
-Select A1:A10. Make sure A1 is the active cell.
- menu Format Conditional Formatting. In the dialog, choose:
-'formula is'
-enter the formula: = (B1=1)
(if formula returns True then applies the special format)
-click the Format button and choose a pattern in the Pattern Tab, say
Grey.
- Clickok
-click ok
From now on when a cell in B1:B10 is 1, it corresponding cell in A1:A10 gets
the Grey pattern

Is it close to what you were looking for?

Regards,
Sebastien

"aseret" wrote:

I am trying to get the cells in one column to show highlighted given the
value in the adjacent cell in the next column =1, if not then no format
change is needed. The macro I have pieced together is listed below but it It
works for one row and then does not drop down to the next row.


Public Sub Highlight()
For Each cell In Selection
If cell.Offset(0, 1) = 1 Then
ActiveCell.Select
Selection.Interior.ColorIndex = 6#
Pattern = xlSolid
End If
Next
End Sub

Macro, if then next
 

Hi

ActiveCell is the "white" cell in your selection, not where the 1 is
discovered. See if this makes sense:

Public Sub Highlight()
Dim cell As Range
For Each cell In Selection
If cell.Offset(0, 1).Value = 1 Then
cell.Interior.ColorIndex = 6#
cell.Interior.Pattern = xlSolid
End If
Next
End Sub

You should select "require variable declaration" in your VB editor. OK, you
must type things like "Dim cell As Range" all the time, but intellisense and
the error messages will be far more helpful and clever.

HTH. Best wishes Harald


"aseret" skrev i melding
...
I am trying to get the cells in one column to show highlighted given the
value in the adjacent cell in the next column =1, if not then no format
change is needed. The macro I have pieced together is listed below but it
It
works for one row and then does not drop down to the next row.


Public Sub Highlight()
For Each cell In Selection
If cell.Offset(0, 1) = 1 Then
ActiveCell.Select
Selection.Interior.ColorIndex = 6#
Pattern = xlSolid
End If
Next
End Sub

Macro, if then next
 

The ActiveCell seemed to be throwing a hitch in things. This code worked
for me:

Public Sub Highlight()
Dim rng As Range
Dim i As Long

Set rng = Selection

For i = 1 To rng.Rows.Count
If rng.Cells(i, 1).Offset(0, 1).Value = 1 Then
rng.Cells(i, 1).Interior.ColorIndex = 6#
End If
Next i

End Sub

Ed

"aseret" wrote in message
...
I am trying to get the cells in one column to show highlighted given the
value in the adjacent cell in the next column =1, if not then no format
change is needed. The macro I have pieced together is listed below but it
It
works for one row and then does not drop down to the next row.


Public Sub Highlight()
For Each cell In Selection
If cell.Offset(0, 1) = 1 Then
ActiveCell.Select
Selection.Interior.ColorIndex = 6#
Pattern = xlSolid
End If
Next
End Sub