Conditional Formatting Multiple Rows Based off 1st Column

A

ajwooden32

New Member
Joined
Feb 5, 2024
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Is there a way to conditional format every other sets of multiple rows based off if a cell in the 1st column has text in it? The text in column A varies so formatting off specific text would require multiple rules.

Below is a manual, created image of what I'm looking for.

1708983011371.png
 

Attachments

  • 1708982881798.png
    1708982881798.png
    30.8 KB · Views: 5

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Yep, you can do this with Conditional Formatting. I will show you for the first 30 rows.
First, select the range A1:E30.
Then go to Conditional Formatting and select the "Use a formula to determine which cells to format" option.
Then enter the following formula:
Excel Formula: 
=isodd(countblank($A$1:$A1))
Then click Format, go to the Fill tab and select your gray color and hit OK twice.

It will look like what you want, i.e.
1708987389679.png
 
Upvote 0
In case you were OK with a VBA option, please try the following on a copy of your workbook. Assumes your data layout is exactly as you've shown. I note that your immage and your attachment are different - if you want the shading to start on row 2 rather than row 1 then change For i = 1... to For i = 2...
VBA Code: 
Option Explicit
Sub ShadesOfGrey()
    Dim LRowD As Long, LRowE As Long, i As Long, j As Long, k As Long
    LRowD = Cells(Rows.Count, 4).End(xlUp).Row
    LRowE = Cells(Rows.Count, 5).End(xlUp).Row
    
    For i = 1 To LRowD
        If Cells(i, 4) <> "" Then
            j = Application.Min(LRowE, Cells(i, 4).End(xlDown).Row - 1)
            If k = 0 Then
                Range(Cells(i, 1), Cells(j, 5)).Interior.ColorIndex = 15
            End If
            k = 1 - k
        End If
    Next i
End Sub

Before:
shades of grey.xlsm
ABCDE
1(blank)(blank)(blank)(blank)(blank)
2(blank)(blank)(blank)(blank)(blank)
3(blank)
4(blank)(blank)(blank)(blank)(blank)
5(blank)
6(blank)
7(blank)
8(blank)
9(blank)
10(blank)
11(blank)(blank)(blank)(blank)(blank)
12(blank)(blank)(blank)(blank)(blank)
13(blank)
14(blank)
15(blank)
16(blank)
17(blank)
18(blank)
19(blank)(blank)(blank)(blank)(blank)
20(blank)
21(blank)
22(blank)
23(blank)
24(blank)
25(blank)
26(blank)
27(blank)
28(blank)(blank)(blank)(blank)(blank)
29(blank)
30(blank)
31(blank)(blank)(blank)(blank)(blank)
32(blank)
33(blank)
34(blank)
35(blank)
36(blank)
37(blank)
38(blank)
39(blank)
40(blank)(blank)(blank)(blank)(blank)
41(blank)
42(blank)
43(blank)
44(blank)
45(blank)
46(blank)
47(blank)
48(blank)
49(blank)
50
Sheet1


After:
shades of grey.xlsm
ABCDE
1(blank)(blank)(blank)(blank)(blank)
2(blank)(blank)(blank)(blank)(blank)
3(blank)
4(blank)(blank)(blank)(blank)(blank)
5(blank)
6(blank)
7(blank)
8(blank)
9(blank)
10(blank)
11(blank)(blank)(blank)(blank)(blank)
12(blank)(blank)(blank)(blank)(blank)
13(blank)
14(blank)
15(blank)
16(blank)
17(blank)
18(blank)
19(blank)(blank)(blank)(blank)(blank)
20(blank)
21(blank)
22(blank)
23(blank)
24(blank)
25(blank)
26(blank)
27(blank)
28(blank)(blank)(blank)(blank)(blank)
29(blank)
30(blank)
31(blank)(blank)(blank)(blank)(blank)
32(blank)
33(blank)
34(blank)
35(blank)
36(blank)
37(blank)
38(blank)
39(blank)
40(blank)(blank)(blank)(blank)(blank)
41(blank)
42(blank)
43(blank)
44(blank)
45(blank)
46(blank)
47(blank)
48(blank)
49(blank)
50
Sheet1
 
Upvote 0
Based on "first column" (column A)

VBA Code: 
Option Explicit
Sub ShadesOfGrey()
    Dim LRowA As Long, LRowE As Long, i As Long, j As Long, k As Long
    LRowA = Cells(Rows.Count, 1).End(xlUp).Row
    LRowE = Cells(Rows.Count, 5).End(xlUp).Row
    
    For i = 1 To LRowA
        If Cells(i, 1) <> "" Then
            j = Application.Min(LRowE, Cells(i, 1).End(xlDown).Row - 1)
            If k = 0 Then
                Range(Cells(i, 1), Cells(j, 5)).Interior.ColorIndex = 15
            End If
            k = 1 - k
        End If
    Next i
End Sub
 
Upvote 0
kevin9999 said:
In case you were OK with a VBA option, please try the following on a copy of your workbook. Assumes your data layout is exactly as you've shown. I note that your immage and your attachment are different - if you want the shading to start on row 2 rather than row 1 then change For i = 1... to For i = 2...
Click to expand...
You could use VBA, but there really is no reason to when it can be done without VBA pretty easily, as I have demonstrated.
I usually recommend against using VBA to do things which can be done without it, unless part of some larger VBA project or they specifically request a VBA solution (looks like they asked for a Conditional Formatting solution in this thread).
No sense in recreating the wheel!
 
Upvote 0
Joe4 said:
Yep, you can do this with Conditional Formatting. I will show you for the first 30 rows.
First, select the range A1:E30.
Then go to Conditional Formatting and select the "Use a formula to determine which cells to format" option.
Then enter the following formula:
Excel Formula: 
=isodd(countblank($A$1:$A1))
Then click Format, go to the Fill tab and select your gray color and hit OK twice.

It will look like what you want, i.e.
View attachment 107519
Click to expand...
I don't have data in every cell in columns A thru C like you are showing. I do in the first row of that particular set of data
kevin9999 said:
In case you were OK with a VBA option, please try the following on a copy of your workbook. Assumes your data layout is exactly as you've shown. I note that your immage and your attachment are different - if you want the shading to start on row 2 rather than row 1 then change For i = 1... to For i = 2...
VBA Code: 
Option Explicit
Sub ShadesOfGrey()
    Dim LRowD As Long, LRowE As Long, i As Long, j As Long, k As Long
    LRowD = Cells(Rows.Count, 4).End(xlUp).Row
    LRowE = Cells(Rows.Count, 5).End(xlUp).Row
  
    For i = 1 To LRowD
        If Cells(i, 4) <> "" Then
            j = Application.Min(LRowE, Cells(i, 4).End(xlDown).Row - 1)
            If k = 0 Then
                Range(Cells(i, 1), Cells(j, 5)).Interior.ColorIndex = 15
            End If
            k = 1 - k
        End If
    Next i
End Sub

Before:
shades of grey.xlsm
ABCDE
1(blank)(blank)(blank)(blank)(blank)
2(blank)(blank)(blank)(blank)(blank)
3(blank)
4(blank)(blank)(blank)(blank)(blank)
5(blank)
6(blank)
7(blank)
8(blank)
9(blank)
10(blank)
11(blank)(blank)(blank)(blank)(blank)
12(blank)(blank)(blank)(blank)(blank)
13(blank)
14(blank)
15(blank)
16(blank)
17(blank)
18(blank)
19(blank)(blank)(blank)(blank)(blank)
20(blank)
21(blank)
22(blank)
23(blank)
24(blank)
25(blank)
26(blank)
27(blank)
28(blank)(blank)(blank)(blank)(blank)
29(blank)
30(blank)
31(blank)(blank)(blank)(blank)(blank)
32(blank)
33(blank)
34(blank)
35(blank)
36(blank)
37(blank)
38(blank)
39(blank)
40(blank)(blank)(blank)(blank)(blank)
41(blank)
42(blank)
43(blank)
44(blank)
45(blank)
46(blank)
47(blank)
48(blank)
49(blank)
50
Sheet1


After:
shades of grey.xlsm
ABCDE
1(blank)(blank)(blank)(blank)(blank)
2(blank)(blank)(blank)(blank)(blank)
3(blank)
4(blank)(blank)(blank)(blank)(blank)
5(blank)
6(blank)
7(blank)
8(blank)
9(blank)
10(blank)
11(blank)(blank)(blank)(blank)(blank)
12(blank)(blank)(blank)(blank)(blank)
13(blank)
14(blank)
15(blank)
16(blank)
17(blank)
18(blank)
19(blank)(blank)(blank)(blank)(blank)
20(blank)
21(blank)
22(blank)
23(blank)
24(blank)
25(blank)
26(blank)
27(blank)
28(blank)(blank)(blank)(blank)(blank)
29(blank)
30(blank)
31(blank)(blank)(blank)(blank)(blank)
32(blank)
33(blank)
34(blank)
35(blank)
36(blank)
37(blank)
38(blank)
39(blank)
40(blank)(blank)(blank)(blank)(blank)
41(blank)
42(blank)
43(blank)
44(blank)
45(blank)
46(blank)
47(blank)
48(blank)
49(blank)
50
Sheet1
Click to expand...

kevin9999 said:
In case you were OK with a VBA option, please try the following on a copy of your workbook. Assumes your data layout is exactly as you've shown. I note that your immage and your attachment are different - if you want the shading to start on row 2 rather than row 1 then change For i = 1... to For i = 2...
VBA Code: 
Option Explicit
Sub ShadesOfGrey()
    Dim LRowD As Long, LRowE As Long, i As Long, j As Long, k As Long
    LRowD = Cells(Rows.Count, 4).End(xlUp).Row
    LRowE = Cells(Rows.Count, 5).End(xlUp).Row
   
    For i = 1 To LRowD
        If Cells(i, 4) <> "" Then
            j = Application.Min(LRowE, Cells(i, 4).End(xlDown).Row - 1)
            If k = 0 Then
                Range(Cells(i, 1), Cells(j, 5)).Interior.ColorIndex = 15
            End If
            k = 1 - k
        End If
    Next i
End Sub

Before:
shades of grey.xlsm
ABCDE
1(blank)(blank)(blank)(blank)(blank)
2(blank)(blank)(blank)(blank)(blank)
3(blank)
4(blank)(blank)(blank)(blank)(blank)
5(blank)
6(blank)
7(blank)
8(blank)
9(blank)
10(blank)
11(blank)(blank)(blank)(blank)(blank)
12(blank)(blank)(blank)(blank)(blank)
13(blank)
14(blank)
15(blank)
16(blank)
17(blank)
18(blank)
19(blank)(blank)(blank)(blank)(blank)
20(blank)
21(blank)
22(blank)
23(blank)
24(blank)
25(blank)
26(blank)
27(blank)
28(blank)(blank)(blank)(blank)(blank)
29(blank)
30(blank)
31(blank)(blank)(blank)(blank)(blank)
32(blank)
33(blank)
34(blank)
35(blank)
36(blank)
37(blank)
38(blank)
39(blank)
40(blank)(blank)(blank)(blank)(blank)
41(blank)
42(blank)
43(blank)
44(blank)
45(blank)
46(blank)
47(blank)
48(blank)
49(blank)
50
Sheet1


After:
shades of grey.xlsm
ABCDE
1(blank)(blank)(blank)(blank)(blank)
2(blank)(blank)(blank)(blank)(blank)
3(blank)
4(blank)(blank)(blank)(blank)(blank)
5(blank)
6(blank)
7(blank)
8(blank)
9(blank)
10(blank)
11(blank)(blank)(blank)(blank)(blank)
12(blank)(blank)(blank)(blank)(blank)
13(blank)
14(blank)
15(blank)
16(blank)
17(blank)
18(blank)
19(blank)(blank)(blank)(blank)(blank)
20(blank)
21(blank)
22(blank)
23(blank)
24(blank)
25(blank)
26(blank)
27(blank)
28(blank)(blank)(blank)(blank)(blank)
29(blank)
30(blank)
31(blank)(blank)(blank)(blank)(blank)
32(blank)
33(blank)
34(blank)
35(blank)
36(blank)
37(blank)
38(blank)
39(blank)
40(blank)(blank)(blank)(blank)(blank)
41(blank)
42(blank)
43(blank)
44(blank)
45(blank)
46(blank)
47(blank)
48(blank)
49(blank)
50
Sheet1
Click to expand...
kevin9999 said:
In case you were OK with a VBA option, please try the following on a copy of your workbook. Assumes your data layout is exactly as you've shown. I note that your immage and your attachment are different - if you want the shading to start on row 2 rather than row 1 then change For i = 1... to For i = 2...
VBA Code: 
Option Explicit
Sub ShadesOfGrey()
    Dim LRowD As Long, LRowE As Long, i As Long, j As Long, k As Long
    LRowD = Cells(Rows.Count, 4).End(xlUp).Row
    LRowE = Cells(Rows.Count, 5).End(xlUp).Row
   
    For i = 1 To LRowD
        If Cells(i, 4) <> "" Then
            j = Application.Min(LRowE, Cells(i, 4).End(xlDown).Row - 1)
            If k = 0 Then
                Range(Cells(i, 1), Cells(j, 5)).Interior.ColorIndex = 15
            End If
            k = 1 - k
        End If
    Next i
End Sub

Before:
shades of grey.xlsm
ABCDE
1(blank)(blank)(blank)(blank)(blank)
2(blank)(blank)(blank)(blank)(blank)
3(blank)
4(blank)(blank)(blank)(blank)(blank)
5(blank)
6(blank)
7(blank)
8(blank)
9(blank)
10(blank)
11(blank)(blank)(blank)(blank)(blank)
12(blank)(blank)(blank)(blank)(blank)
13(blank)
14(blank)
15(blank)
16(blank)
17(blank)
18(blank)
19(blank)(blank)(blank)(blank)(blank)
20(blank)
21(blank)
22(blank)
23(blank)
24(blank)
25(blank)
26(blank)
27(blank)
28(blank)(blank)(blank)(blank)(blank)
29(blank)
30(blank)
31(blank)(blank)(blank)(blank)(blank)
32(blank)
33(blank)
34(blank)
35(blank)
36(blank)
37(blank)
38(blank)
39(blank)
40(blank)(blank)(blank)(blank)(blank)
41(blank)
42(blank)
43(blank)
44(blank)
45(blank)
46(blank)
47(blank)
48(blank)
49(blank)
50
Sheet1


After:
shades of grey.xlsm
ABCDE
1(blank)(blank)(blank)(blank)(blank)
2(blank)(blank)(blank)(blank)(blank)
3(blank)
4(blank)(blank)(blank)(blank)(blank)
5(blank)
6(blank)
7(blank)
8(blank)
9(blank)
10(blank)
11(blank)(blank)(blank)(blank)(blank)
12(blank)(blank)(blank)(blank)(blank)
13(blank)
14(blank)
15(blank)
16(blank)
17(blank)
18(blank)
19(blank)(blank)(blank)(blank)(blank)
20(blank)
21(blank)
22(blank)
23(blank)
24(blank)
25(blank)
26(blank)
27(blank)
28(blank)(blank)(blank)(blank)(blank)
29(blank)
30(blank)
31(blank)(blank)(blank)(blank)(blank)
32(blank)
33(blank)
34(blank)
35(blank)
36(blank)
37(blank)
38(blank)
39(blank)
40(blank)(blank)(blank)(blank)(blank)
41(blank)
42(blank)
43(blank)
44(blank)
45(blank)
46(blank)
47(blank)
48(blank)
49(blank)
50
Sheet1
Click to expand...
Your example is exactly what I'm looking for but after creating the module in VBA what do I do to get it to work?
 
Upvote 0
ajwooden32 said:
I don't have data in every cell in columns A thru C like you are showing. I do in the first row of that particular set of data
Click to expand...

Did you even try my solution before dismissing it?

You had said:
Is there a way to conditional format every other sets of multiple rows based off if a cell in the 1st column has text in it?
Click to expand...

The other columns don't matter. My formula is strictly looking at the values in column A (1st column), like you said you wanted.
Try it and see!
 
Upvote 0
Joe4 said:
Did you even try my solution before dismissing it?

You had said:


The other columns don't matter. My formula is strictly looking at the values in column A (1st column), like you said you wanted.
Try it and see
Click to expand...

The image on the left (1st image) is your formula. The image on the right (2nd image) is what I'm looking for.

1709063335379.png
1709063413667.png
 
Upvote 0
ajwooden32 said:
Your example is exactly what I'm looking for but after creating the module in VBA what do I do to get it to work?
Click to expand...
Once you place the code into a standard module, you can run it by pressing the F5 key, or by clicking Run, or by pressing the run button. in the VB Editor. To display the VB Editor press Alt+F11. If you don't have the Developer menu available on your ribbon, you can add it via File/Options/Customise Ribbon - and check the box next to Developer. There's a number of ways to run VBA code - see this for more ideas.

I've added code to my suggestion to accommodate changes in your data if you run it a second & subsequent number of times:

VBA Code: 
Option Explicit
Sub ShadesOfGrey()
    Dim LRowA As Long, LRowE As Long, i As Long, j As Long, k As Long
    LRowA = Cells(Rows.Count, 1).End(xlUp).Row
    LRowE = Cells(Rows.Count, 5).End(xlUp).Row
    
    For i = 1 To LRowA
        If Cells(i, 1) <> "" Then
            j = Application.Min(LRowE, Cells(i, 1).End(xlDown).Row - 1)
            If k = 0 Then
                Range(Cells(i, 1), Cells(j, 5)).Interior.ColorIndex = 15
            Else
                Range(Cells(i, 1), Cells(j, 5)).Interior.Color = xlNone
            End If
            k = 1 - k
        End If
    Next i
End Sub
 
Upvote 0
ajwooden32 said:
The image on the left (1st image) is your formula. The image on the right (2nd image) is what I'm looking for.

View attachment 107590 View attachment 107594
Click to expand...
Your images don't seem to make much sense. You have blanks and the word "blanks". So where exactly is any data?
Could you please post a more realistic image with actual data in it to clarify it? Just be sure to "dummy up" any sensitive/personal data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Formatting cells filled by array formulae
Replies
5
Views
137
Ludwig
L
D
  • Question Question
Static Timestamp
Replies
4
Views
949
Fluff
Fluff
A
  • Solved
Conditional Formatting - apply rules to multiple rows based on specific cells (drop down menus)
Replies
4
Views
563
AndyB86
A
A
  • Question Question
Replacing a cell value using VBA code
Replies
4
Views
178
RobP
R
C
  • Question Question
Conditional Formatting
Replies
5
Views
323
ChristineC
C

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top