I have two columns of data.

I want to programmatically create conditional formatting on the second
column where the color of the cell is based on the value of the cell to its
left. This would apply to the whole range of the second column.

I can see how to do this if I was basing the formatting on the same cell as
the formatting is to be applied to.

What is the syntax in the macro to do the formatting based on the value of
the cells in the first column?

Thanks in advance for any help you can offer!

Steve

Thanks for your reply.

But doesn't this compare the cell in the right column against the cell in
the left column?

What if I wanted the formatting of the right-column cell to be dependent on
comparing the cell in the left-column cell to some value?

(sorry if I'm misunderstanding your code).

Steve

"A Harvey" wrote in message
...
Steve,
This code is very basic but would do as you ask. Add
looping or change values as desired to achieve the desired
result (Code start)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/14/2003 by Harvey

Row = 4
col = 3
comval = Cells(Row, col - 1)
Cells(Row, col).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:=comval
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub

(code end)

Hope this helps..... A. H.




-----Original Message-----
I have two columns of data.

I want to programmatically create conditional formatting
on the second
column where the color of the cell is based on the value
of the cell to its
left. This would apply to the whole range of the second
column.

I can see how to do this if I was basing the formatting
on the same cell as
the formatting is to be applied to.

What is the syntax in the macro to do the formatting
based on the value of
the cells in the first column?

Thanks in advance for any help you can offer!

Steve


.

Steve,

The easiest way is to Record a macro while you do the formatting.
For me to be more specific I would need to know the relationship you are
looking for: = to, , < , or what?

steve

"Steve" wrote in message
.. .
Thanks for your reply.

But doesn't this compare the cell in the right column against the cell in
the left column?

What if I wanted the formatting of the right-column cell to be dependent
on
comparing the cell in the left-column cell to some value?

(sorry if I'm misunderstanding your code).

Steve

"A Harvey" wrote in message
...
Steve,
This code is very basic but would do as you ask. Add
looping or change values as desired to achieve the desired
result (Code start)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/14/2003 by Harvey

Row = 4
col = 3
comval = Cells(Row, col - 1)
Cells(Row, col).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:=comval
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub

(code end)

Hope this helps..... A. H.




-----Original Message-----
I have two columns of data.

I want to programmatically create conditional formatting
on the second
column where the color of the cell is based on the value
of the cell to its
left. This would apply to the whole range of the second
column.

I can see how to do this if I was basing the formatting
on the same cell as
the formatting is to be applied to.

What is the syntax in the macro to do the formatting
based on the value of
the cells in the first column?

Thanks in advance for any help you can offer!

Steve


.

Steve,

Basically, I have 2 cells hidden that contains a lower limit and higher
limit, say 2.3 and 3.6.

Then, in one column C I have values, say 1, 3.2, 4, 0
In Column D I have cells where I want the color of the cell to be dependent
on a comparison.

If the respective cell to the left of the one in D (i.e., in column C) is
less than the upper limit and greater than the lower limit, the cell in D is
yellow. If it's lower than the lower limit, it's red. If it's higher than
the upper limit, than it's green.

Does that help?

Thanks!
Steve

"steve" wrote in message
...
Steve,

The easiest way is to Record a macro while you do the formatting.
For me to be more specific I would need to know the relationship you are
looking for: = to, , < , or what?

steve

"Steve" wrote in message
.. .
Thanks for your reply.

But doesn't this compare the cell in the right column against the cell
in
the left column?

What if I wanted the formatting of the right-column cell to be dependent
on
comparing the cell in the left-column cell to some value?

(sorry if I'm misunderstanding your code).

Steve

"A Harvey" wrote in message
...
Steve,
This code is very basic but would do as you ask. Add
looping or change values as desired to achieve the desired
result (Code start)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/14/2003 by Harvey

Row = 4
col = 3
comval = Cells(Row, col - 1)
Cells(Row, col).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:=comval
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub

(code end)

Hope this helps..... A. H.




-----Original Message-----
I have two columns of data.

I want to programmatically create conditional formatting
on the second
column where the color of the cell is based on the value
of the cell to its
left. This would apply to the whole range of the second
column.

I can see how to do this if I was basing the formatting
on the same cell as
the formatting is to be applied to.

What is the syntax in the macro to do the formatting
based on the value of
the cells in the first column?

Thanks in advance for any help you can offer!

Steve


.

Actually, how would I do the following (should be simpler).

I've never used a formula in conditional formatting, so that's why I'm a bit
dim on this one.

I want to conditionally format a whole range (a column).

If the cell has a "1" in it, I want the cell to be red.
If the cell has a "2" in it, I want the cell to be black.

Thanks again,
Steve

"steve" wrote in message
...
Steve,

The easiest way is to Record a macro while you do the formatting.
For me to be more specific I would need to know the relationship you are
looking for: = to, , < , or what?

steve

"Steve" wrote in message
.. .
Thanks for your reply.

But doesn't this compare the cell in the right column against the cell
in
the left column?

What if I wanted the formatting of the right-column cell to be dependent
on
comparing the cell in the left-column cell to some value?

(sorry if I'm misunderstanding your code).

Steve

"A Harvey" wrote in message
...
Steve,
This code is very basic but would do as you ask. Add
looping or change values as desired to achieve the desired
result (Code start)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/14/2003 by Harvey

Row = 4
col = 3
comval = Cells(Row, col - 1)
Cells(Row, col).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:=comval
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub

(code end)

Hope this helps..... A. H.




-----Original Message-----
I have two columns of data.

I want to programmatically create conditional formatting
on the second
column where the color of the cell is based on the value
of the cell to its
left. This would apply to the whole range of the second
column.

I can see how to do this if I was basing the formatting
on the same cell as
the formatting is to be applied to.

What is the syntax in the macro to do the formatting
based on the value of
the cells in the first column?

Thanks in advance for any help you can offer!

Steve


.

It can be done, but comparing is very vague. Why not spell out what the
conditions are.

In general, select B1:B whatever with B1 as the Active Cell

Then do format=Conditional Formatting, change "Cell is" to "formula is" by
selecting it in the dropdown.

in the textbox to the left put in your formula - something like

=B1A1
or
=And(B1<"",A1<"",B1A1)

then select your formatting.

--
Regards,
Tom Ogilvy


"Steve" wrote in message
.. .
Thanks for your reply.

But doesn't this compare the cell in the right column against the cell in
the left column?

What if I wanted the formatting of the right-column cell to be dependent
on
comparing the cell in the left-column cell to some value?

(sorry if I'm misunderstanding your code).

Steve

"A Harvey" wrote in message
...
Steve,
This code is very basic but would do as you ask. Add
looping or change values as desired to achieve the desired
result (Code start)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/14/2003 by Harvey

Row = 4
col = 3
comval = Cells(Row, col - 1)
Cells(Row, col).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:=comval
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub

(code end)

Hope this helps..... A. H.




-----Original Message-----
I have two columns of data.

I want to programmatically create conditional formatting
on the second
column where the color of the cell is based on the value
of the cell to its
left. This would apply to the whole range of the second
column.

I can see how to do this if I was basing the formatting
on the same cell as
the formatting is to be applied to.

What is the syntax in the macro to do the formatting
based on the value of
the cells in the first column?

Thanks in advance for any help you can offer!

Steve


.

so you don't want to be dependent on the column to the left - you now only
have one column.

Select the whole column (let us say B) with the first cell as the active
cell (B1).

Format=conditional formatting
leave "Cell Value is"
in the next block, from the dropdown select "equal to"
in the next box put in 1

click the format button, choose pattern and select red

now click OK and then Add

leave Cell Value is
in the next block, from the dropdown select "equal to"
in the next box put in 2

click the format button, choose pattern and black
then font and change the color to white (if you want to see the number)

OK your way out.

--
Regards,
Tom Ogilvy





"Steve" wrote in message
. ..
Actually, how would I do the following (should be simpler).

I've never used a formula in conditional formatting, so that's why I'm a
bit
dim on this one.

I want to conditionally format a whole range (a column).

If the cell has a "1" in it, I want the cell to be red.
If the cell has a "2" in it, I want the cell to be black.

Thanks again,
Steve

"steve" wrote in message
...
Steve,

The easiest way is to Record a macro while you do the formatting.
For me to be more specific I would need to know the relationship you are
looking for: = to, , < , or what?

steve

"Steve" wrote in message
.. .
Thanks for your reply.

But doesn't this compare the cell in the right column against the cell
in
the left column?

What if I wanted the formatting of the right-column cell to be
dependent
on
comparing the cell in the left-column cell to some value?

(sorry if I'm misunderstanding your code).

Steve

"A Harvey" wrote in message
...
Steve,
This code is very basic but would do as you ask. Add
looping or change values as desired to achieve the desired
result (Code start)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/14/2003 by Harvey

Row = 4
col = 3
comval = Cells(Row, col - 1)
Cells(Row, col).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:=comval
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub

(code end)

Hope this helps..... A. H.




-----Original Message-----
I have two columns of data.

I want to programmatically create conditional formatting
on the second
column where the color of the cell is based on the value
of the cell to its
left. This would apply to the whole range of the second
column.

I can see how to do this if I was basing the formatting
on the same cell as
the formatting is to be applied to.

What is the syntax in the macro to do the formatting
based on the value of
the cells in the first column?

Thanks in advance for any help you can offer!

Steve


.

Yes, I guess that was easy after all! :-)

Thanks.

"Tom Ogilvy" wrote in message
...
so you don't want to be dependent on the column to the left - you now only
have one column.

Select the whole column (let us say B) with the first cell as the active
cell (B1).

Format=conditional formatting
leave "Cell Value is"
in the next block, from the dropdown select "equal to"
in the next box put in 1

click the format button, choose pattern and select red

now click OK and then Add

leave Cell Value is
in the next block, from the dropdown select "equal to"
in the next box put in 2

click the format button, choose pattern and black
then font and change the color to white (if you want to see the number)

OK your way out.

--
Regards,
Tom Ogilvy





"Steve" wrote in message
. ..
Actually, how would I do the following (should be simpler).

I've never used a formula in conditional formatting, so that's why I'm a
bit
dim on this one.

I want to conditionally format a whole range (a column).

If the cell has a "1" in it, I want the cell to be red.
If the cell has a "2" in it, I want the cell to be black.

Thanks again,
Steve

"steve" wrote in message
...
Steve,

The easiest way is to Record a macro while you do the formatting.
For me to be more specific I would need to know the relationship you
are
looking for: = to, , < , or what?

steve

"Steve" wrote in message
.. .
Thanks for your reply.

But doesn't this compare the cell in the right column against the
cell
in
the left column?

What if I wanted the formatting of the right-column cell to be
dependent
on
comparing the cell in the left-column cell to some value?

(sorry if I'm misunderstanding your code).

Steve

"A Harvey" wrote in message
...
Steve,
This code is very basic but would do as you ask. Add
looping or change values as desired to achieve the desired
result (Code start)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/14/2003 by Harvey

Row = 4
col = 3
comval = Cells(Row, col - 1)
Cells(Row, col).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:=comval
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub

(code end)

Hope this helps..... A. H.




-----Original Message-----
I have two columns of data.

I want to programmatically create conditional formatting
on the second
column where the color of the cell is based on the value
of the cell to its
left. This would apply to the whole range of the second
column.

I can see how to do this if I was basing the formatting
on the same cell as
the formatting is to be applied to.

What is the syntax in the macro to do the formatting
based on the value of
the cells in the first column?

Thanks in advance for any help you can offer!

Steve


.