Highlighting a range of cells only if any cell in that range contains any text.

[amaresh achar]

Hello,

I am looking for a conditional formatting formula to highlight a range of cells only if any cell in that range contains any text, (as explained below):

I have a cell range J23:S23. If any cell among this range contains any text, then entire cell range J23:S23 should be highlighted in yellow.

I want format paint this formula, to 1000 such ranges up to J1036:S1036 (if I get formula for single range)

Or even a single formula (if possible) to cover entire 1000 rows also welcome...

Thanks in Advance...

 

[Herakles]

Hello,

I am looking for a conditional formatting formula to highlight a range of cells only if any cell in that range contains any text, (as explained below):

I have a cell range J23:S23. If any cell among this range contains any text, then entire cell range J23:S23 should be highlighted in yellow.

I want format paint this formula, to 1000 such ranges up to J1036:S1036 (if I get formula for single range)

Or even a single formula (if possible) to cover entire 1000 rows also welcome...

Thanks in Advance...

=COUNTA($J$23:$S$23)>0

1000 seperate ranges up to J1036:S1036?

Can you share an an example using XL2BB?

 

[amaresh achar]

=COUNTA($J$23:$S$23)>0

1000 seperate ranges up to J1036:S1036?

Can you share an an example using XL2BB?

Sorry... I don't know how to use XL2BB. I will look into it for future postings... but now I can explain as below. I hope its not dumb to explain like this

J23:S23 (1st range)
J24:S24 (2nd range)
J25:S25 (3rd range)
J26:S26 (4th range)
J27:S27 (5th range)
.
.
.
.
.
J1036:S1036 (1014th range)

 

[amaresh achar]

Hello,

I am looking for a conditional formatting formula to highlight a range of cells only if any cell in that range contains any text, (as explained below):

I have a cell range J23:S23. If any cell among this range contains any text, then entire cell range J23:S23 should be highlighted in yellow.

I want format paint this formula, to 1000 such ranges up to J1036:S1036 (if I get formula for single range)

Or even a single formula (if possible) to cover entire 1000 rows also welcome...

Thanks in Advance...

Ohhhh.. OK... I was wrong.. Sorry... I cant format paint this formula... as I already have different conditional formatting formula for every alternate rows... I need single formula to perform this conditional formatting for all these 1014 rows.

 

[Herakles]

Apply this the entire range.

=COUNTA($J23:$S23)>0

 

[amaresh achar]

Apply this the entire range.

=COUNTA($J23:$S23)>0

I tried... but its highlighting entire range J23:S1036 (if any text is found in any cell among J23:S23)

I need to highlight only J23:S23 if any text is found in any cell among J23:S23 similarly..
need to highlight only J24:S24 if any text is found in any cell among J24:S24 similarly..
need to highlight only J25:S25 if any text is found in any cell among J25:S25 similarly..
need to highlight only J26:S26 if any text is found in any cell among J26:S26 similarly..
need to highlight only J27:S27 if any text is found in any cell among J27:S27 similarly..
.
.
.
.
.
.
need to highlight only J1036:S1036 if any text is found in any cell among J1036:S1036..

 

[Herakles]

It works for me, see image.

Select the range J23:S1036.

Create a new rule using the ‘Use a formula to determine which cells to format’ Rule Type.

Apply the =COUNTA($J23:$S23)>0 formula.

 

[amaresh achar]

It works for me, see image.

Select the range J23:S1036.

Create a new rule using the ‘Use a formula to determine which cells to format’ Rule Type.

Apply the =COUNTA($J23:$S23)>0 formula.

Oh.. OK .. now I see where I did the mistake... before going to conditional formatting, First I have to select the range J23:S1036.. got it...

But when I select the range J23:S1036... the excel will freeze (because I already have 4 set of conditional formatting rule for each cell)

Any remedy for that...?? (If you want to look into that.. then I will share a G-drive link...)

 

[Herakles]

Read this page.

More than Three Conditional Formats in Excel

Excel has a limit of three conditional formats per cell or range of cells because each conditional format requires additional processing power. If you apply too many conditional formats, it can slow down your spreadsheet and make it more difficult to work with. Additionally, having too many conditional formats can make your spreadsheet look cluttered and confusing.

 

[amaresh achar]

Read this page.

More than Three Conditional Formats in Excel

Excel has a limit of three conditional formats per cell or range of cells because each conditional format requires additional processing power. If you apply too many conditional formats, it can slow down your spreadsheet and make it more difficult to work with. Additionally, having too many conditional formats can make your spreadsheet look cluttered and confusing.

Thanks... I learnt a new thing today... I will try to optimize my existing formula.. and thanks for the solution.