Please or to access all these features

Add post

Watch this thread

Save thread

Start a new thread

Flip thread

Hide thread

My feed

Active Unanswered threads

Getting started FAQ's

Unanswered threads Acronyms Talk guidelines

Hide shortcut buttons

Talk

Geeky stuff

Flip

1 2

Original poster

Highlight some cells in a row automatically

40 replies

ExcelHelpPlease · 14/03/2023 15:14

Hi All,

Can someone help me highlight columns A to AR grey, in individual rows, if I type '/ into column B?

I found a some instructions online using conditonal formatting, but I can't make it work 😪

Thank you!

OP posts:

Thread gallery

Original poster

ExcelHelpPlease · 14/03/2023 16:40

I tried that, I had to edit it to this:

Can someone help me highlight columns A to AR grey, in individual rows, if I type '/ into column B?

and it only highlighted the one cell 😫

OP posts:

BreadInCaptivity · 14/03/2023 17:29

What range are you using in the applies to box?

BreadInCaptivity · 14/03/2023 17:30

Can you photo the formula and range you are using?

BreadInCaptivity · 14/03/2023 17:36

In the applied to section, assuming row 1 is your header row,

The formula you should have in "applies to". Should be =$a$2:$ar$6

This will format up to row 6.

Obviously change the 6 depending on how many rows you have.

Namechangingagain111 · 14/03/2023 21:18

ExcelHelpPlease · 14/03/2023 15:40

Thanks, I'm looking at that now, but its very wordy. I normally google what I want to do and find a formula or macro thing to c&p and edit, but none of thjis is clear what I should edit.

Show quote history

This is the bit that was the most useful (for me anyway !)

Highlight some cells in a row automatically

OliveHenry · 14/03/2023 23:28

OliveHenry · 14/03/2023 15:35

Try this - I formatted all cells in the selection with the same conditional formatting rule.

In the formula you need to use $B to fix it so it always looks at column B. But don't fix the row num (so leave as 1 not $1) so that the formula follows the rows.

Hope that makes sense!

Did you try this @ExcelHelpPlease ?

Original poster

ExcelHelpPlease · 15/03/2023 10:31

BreadInCaptivity · 14/03/2023 17:36

In the applied to section, assuming row 1 is your header row,

The formula you should have in "applies to". Should be =$a$2:$ar$6

This will format up to row 6.

Obviously change the 6 depending on how many rows you have.

Its still not working for me.

I opened my spreadsheet.
I clicked conditional formatting.
I clicked new rule.
I clicked use a formula to determine which cells to format.
I pasted =$a$2:$ar$6.
I changed the 2 to a 3 and the 6 to 2000.
I got the attached notification.

OP posts:

Original poster

ExcelHelpPlease · 15/03/2023 10:34

OliveHenry · 14/03/2023 23:28

Did you try this @ExcelHelpPlease ?

Show quote history

I didn't. I'm stupid, I need step by step instructions in this format:

Open spreadsheet.
Click conditional formatting.
Click new rule.
Click use a formula to determine which cells to format.
Paste =$a$2:$ar$6.
Change the 2 to a 3 and the 6 to 2000.

OP posts:

RoseslnTheHospital · 15/03/2023 10:58

When you add the rule, you need to put in the condition, not the cells you want the format to apply to. Have a look at the first screen shot.

Then, when you've done that and clicked OK, go back into Manage Rules, make sure the dropdown says for "This Worksheet" not "Current Selection" and then edit the rule to give the range of cells you want the formatting to apply to. See the second screen shot.

Hope that helps!