Conditional Formatting

  • Thread starter Thread starter Brandon
  • Start date Start date
B

Brandon

I know that you can do "less than" conditional formatting, but can you do
conditional formatting if it is like this:
if (value not currently active) < (cell currently active) then
[do formatting]
 
Maybe...

I created a table (A1:H20) and selected that table.
With A1 the activecell, I could use:

Format|Conditional formatting
formula is:
=A1>INDIRECT(CELL("address"))

But I had to make sure that excel recalculated--just selecting the cell won't be
enough.

I just hit F2 and enter to force that recalc.

Another way to force a recalculation is by using a worksheet event.

If you want to try that, you could rightclick on the worksheet tab, select view
code and paste this in:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Calculate
End Sub

or you could be even more explicit:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("A1:H20")) Is Nothing Then
'do nothing
Else
Me.Calculate
End If
End Sub

I know that you can do "less than" conditional formatting, but can you do
conditional formatting if it is like this:
if (value not currently active) < (cell currently active) then
[do formatting]
Click to expand...
 
That should work, but can I also do somehting like that in more than 1 cell?
Say, a column?

Dave Peterson said:
Maybe...

I created a table (A1:H20) and selected that table.
With A1 the activecell, I could use:

Format|Conditional formatting
formula is:
=A1>INDIRECT(CELL("address"))

But I had to make sure that excel recalculated--just selecting the cell
won't be
enough.

I just hit F2 and enter to force that recalc.

Another way to force a recalculation is by using a worksheet event.

If you want to try that, you could rightclick on the worksheet tab, select
view
code and paste this in:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Calculate
End Sub

or you could be even more explicit:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("A1:H20")) Is Nothing Then
'do nothing
Else
Me.Calculate
End If
End Sub

I know that you can do "less than" conditional formatting, but can you do
conditional formatting if it is like this:
if (value not currently active) < (cell currently active) then
[do formatting]
Click to expand...
Click to expand...
 
I don't understand.

Are you applying the data|validation to a whole column? My example used A1:H20
(multiple columns--not entire columns, though).

And you can only have one activecell per window.
That should work, but can I also do somehting like that in more than 1 cell?
Say, a column?

Dave Peterson said:
Maybe...

I created a table (A1:H20) and selected that table.
With A1 the activecell, I could use:

Format|Conditional formatting
formula is:
=A1>INDIRECT(CELL("address"))

But I had to make sure that excel recalculated--just selecting the cell
won't be
enough.

I just hit F2 and enter to force that recalc.

Another way to force a recalculation is by using a worksheet event.

If you want to try that, you could rightclick on the worksheet tab, select
view
code and paste this in:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Calculate
End Sub

or you could be even more explicit:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("A1:H20")) Is Nothing Then
'do nothing
Else
Me.Calculate
End If
End Sub

I know that you can do "less than" conditional formatting, but can you do
conditional formatting if it is like this:
if (value not currently active) < (cell currently active) then
[do formatting]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top