conditional formatting based on relative cell value
 

I'm trying to clear the strikethrough of a specific non-continuguous subset
of merged cells based on the value in the prior column. Specifically
$Q$9:$U$9 based on $P$9, $Q$11:$U$11 based on $P$11, etc.

I've tried selecting all of the merged cells using Ctrl key to select
non-contiguous ranges and then using the conditional formatting rule when the
last merged cell $Q$49:$U$49 was active:

=AND(ISNUMBER($P$49),$P$490)

but that formula sets or clears the strikethrough for all of the merged
cells based on the single row rather than all of them.


I can do it one row at a time, but setting up a separate rule for each row,
but that's an incredibly tedious and silly way to do it (using formula above
for each relevant row).


What I want to do is have a general formula that I can use, such as the
following rather convoluted one, that doesn't seem to work:

=AND(ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN()-1))),INDIRECT(ADDRESS(ROW(),COLUMN()-1))0)

I'm sure there's a way to do this, but I've not discovered it yet.
Suggestions welcome, and thank you in advance.

conditional formatting based on relative cell value
 

still learning wrote:
I'm trying to clear the strikethrough of a specific non-continuguous subset
of merged cells based on the value in the prior column. Specifically
$Q$9:$U$9 based on $P$9, $Q$11:$U$11 based on $P$11, etc.

I've tried selecting all of the merged cells using Ctrl key to select
non-contiguous ranges and then using the conditional formatting rule when the
last merged cell $Q$49:$U$49 was active:

=AND(ISNUMBER($P$49),$P$490)

but that formula sets or clears the strikethrough for all of the merged
cells based on the single row rather than all of them.


I can do it one row at a time, but setting up a separate rule for each row,
but that's an incredibly tedious and silly way to do it (using formula above
for each relevant row).


What I want to do is have a general formula that I can use, such as the
following rather convoluted one, that doesn't seem to work:

=AND(ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN()-1))),INDIRECT(ADDRESS(ROW(),COLUMN()-1))0)

I'm sure there's a way to do this, but I've not discovered it yet.
Suggestions welcome, and thank you in advance.


If you don't want the row fixed...

=AND(ISNUMBER($P49),$P490)

conditional formatting based on relative cell value
 

Glenn: Thank you for the speedy reply and straightforward answer of selecting
cells and using $P49 to have relative rows rather than $P$49 having fixed
cell in my original attempt. This is what I needed.

"Glenn" wrote:

still learning wrote:
I'm trying to clear the strikethrough of a specific non-continuguous subset
of merged cells based on the value in the prior column. Specifically
$Q$9:$U$9 based on $P$9, $Q$11:$U$11 based on $P$11, etc.

I've tried selecting all of the merged cells using Ctrl key to select
non-contiguous ranges and then using the conditional formatting rule when the
last merged cell $Q$49:$U$49 was active:

=AND(ISNUMBER($P$49),$P$490)

but that formula sets or clears the strikethrough for all of the merged
cells based on the single row rather than all of them.


I can do it one row at a time, but setting up a separate rule for each row,
but that's an incredibly tedious and silly way to do it (using formula above
for each relevant row).


What I want to do is have a general formula that I can use, such as the
following rather convoluted one, that doesn't seem to work:

=AND(ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN()-1))),INDIRECT(ADDRESS(ROW(),COLUMN()-1))0)

I'm sure there's a way to do this, but I've not discovered it yet.
Suggestions welcome, and thank you in advance.


If you don't want the row fixed...

=AND(ISNUMBER($P49),$P490)