Applying Conditional Formatting across 100s of Cells

[Thomas5000]

I have a row of records. Underneath each row are criteria that the records must meet. If i put an N in one of the spaces, I would like for the record number to highlight in a different color.

The formular i have is =COUNTIF($B$7:$B$24,"N")and it works BUT ONLY FOR THAT ONE COLUMN. How do i apply this across 1,000 columns without having to do it one-by-one.

I want the B column to highlight based on criteria underneath it. I want the C column record numbers to highlight based on the information under it and so forth. Doing to manually would take too long. Plus i have 40 sheets per workbook.

Thanks!

 

[SkipVought]

Hi,

Select the entire range of cells you want to CF.
[tt]
Format > CF

Formula is: =
then select the cell below and make the reference RELATIVE. If that is B7...

Formula is: =B7="N"
[/tt]
complete the FORMAT and OK out.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Thomas5000]

Thanks but i don't know how to make it relative. Also I'm checking a range of cells in each column about 10 or so not just one.

I selected teh range of record numbers. I clicked Conditional formatting. Under Formular IS I clicked and so that an equal sign appeared. I clicked B7 (rather than the range B7:B24) so that the formular was =B7="N". All the records changed colors based on the first B7 not based on their own column.

I see nothing about relativity.

Thanks! PLEASE HELP!

 

[xlbo]

Press F1
Enter "Relative" into search box
Read appropriate answer and apply
If you have further questions on how to implement, ask.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Thomas5000]

Thanks Geoff. That helped.

It has to do with relative cell reference. I typed this into the Search on MS Excel and clicked on "Switch between relative, absolute, and mixed references"

The Dollar Sign defines what is relative and what is absolute. So if i want the column to be Relative, then i remove the $ from in front of the reference. If i want the row to be relative or to change, then i remove the $ from in front of the row number.

So my formular came out to be =COUNTIF(B$7:B$24,"N") without the dollar sign in front of the B column. Instead of $B$7:$B$24 it is B$7:B$24. If i wanted the Column to stay absolute and the row to be relative i would say
$B7:$B24. if i want both to be relative i would say B7:B24.

 

[xlbo]

correct !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

What is the RANGE that you are formatting and what is the RANGE of the criteia values?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Thomas5000]

The range was B7:B24 but i had several rows of records. I went to the right most end of the worksheet and started over on the next row. So I had 60 rows with what 17 criteria each.

 

[SkipVought]

How does, "Applying Conditional Formatting across 100s of Cells" reconcile with "The range was B7:B24 "

I'm not getting the picture and you're not explaining very clearly what you are trying to do.

Please start again explaining WHAT you want to do without telling us HOW you are trying to do it.

Elsplain it CLEARLY, CONCISELY and COMPLETELY.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[xlbo]

Skip - sounds like the OP has solved their issue...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question