# Fill D1:D4 format all the way below

#### motilulla

##### Well-known Member
Hello,

Here is original data

Book1
CDE
1A - A
2A - B
3C - A
4C - C
5
6C - A
7C - C
8C - A
9A - A
10A - B
11C - A
12C - A
13A - B
14A - A
15A - B
16C - A
17A - A
18A - A
19C - A
20A - A
21A - A
22A - A
23A - A
24C - A
25A - A
26C - A
27C - C
28A - B
29C - C
30C - C
31A - A
32
Hoja1

I want to fill D1:D4 format as per text find all the way below D6 to end of the column

Example after i want it looks like as below

Book1
CDE
1A - A
2A - B
3C - A
4C - C
5
6C - A
7C - C
8C - A
9A - A
10A - B
11C - A
12C - A
13A - B
14A - A
15A - B
16C - A
17A - A
18A - A
19C - A
20A - A
21A - A
22A - A
23A - A
24C - A
25A - A
26C - A
27C - C
28A - B
29C - C
30C - C
31A - A
32
Hoja2

Thank you all

Excel 2000
Regards,
Moti

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Matt Rogers

##### Well-known Member
Hi.

Highlight the range, start Conditional formatting and click "Use a formula to ..."

Code:
``````1  Formula is =D1="C - C"
2  Formula is =D1="C - A"
3  Formula is =OR(D1="A - A",D1="A - B")``````
Choose the color.

#### Tetra201

##### MrExcel MVP
Here is a set of rules for conditional formatting of the entire range:

Green fill =LEFT(\$D1)="A"
Ref fill =LEFT(\$D1)="C"
White font =RIGHT(\$D1)="C"

#### motilulla

##### Well-known Member
Thank you Matt Rogers, yes conditional formatting is working, but how could it be done by VBA
Because really format is in cells D1:D4 are different as looks let me if I can post as the look

D
1
A - A
2
A - B
3
C - A
4
C - C

<tbody>
</tbody>
Hoja1

Regards,
Moti

Last edited:

#### motilulla

##### Well-known Member

Here is a set of rules for conditional formatting of the entire range:

Green fill =LEFT(\$D1)="A"
Ref fill =LEFT(\$D1)="C"
White font =RIGHT(\$D1)="C"

Thank you Tetra201, yes it works but as you see Post#4

A-A background Green & Font Black
A-B background Green A= black Font & B=Red font
C-A background Red C=White Font & A=Lime Font
C-C background Red & Font White

I think this only could be possible only with VBA

Regards,
Moti

#### Michael M

##### Well-known Member
Dont have excel at the moment, so is untested

Code:
``````Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
For r = 6 To lr
If Range("C" & r).Value = Range("C1") Then
Range("C1").Copy Range("C" & r)
ElseIf Range("C" & r).Value = Range("C2") Then
Range("C2").Copy Range("C" & r)
ElseIf Range("C" & r).Value = Range("C3") Then
Range("C3").Copy Range("C" & r)
ElseIf Range("C" & r).Value = Range("C4") Then
Range("C4").Copy Range("C" & r)
End If
Next r
End Sub``````

#### Tetra201

##### MrExcel MVP

Thank you Tetra201, yes it works but as you see Post#4

A-A background Green & Font Black
A-B background Green A= black Font & B=Red font
C-A background Red C=White Font & A=Lime Font
C-C background Red & Font White

I think this only could be possible only with VBA
Here is a tested-to-work code:
Code:
``````Sub ColorCell()
For Each cll In Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row)
With cll
Select Case .Value
Case "A - A": .Interior.Color = vbGreen
Case "C - C": .Interior.Color = vbRed
Case "A - B": .Interior.Color = vbGreen
.Characters(Start:=5, Length:=1).Font.Color = vbRed
Case "C - A": .Interior.Color = vbRed
.Characters(Start:=1, Length:=1).Font.Color = vbWhite
.Characters(Start:=5, Length:=1).Font.Color = vbGreen
End Select
End With
Next cll
End Sub``````

#### motilulla

##### Well-known Member
Dont have excel at the moment, so is untested

Code:
``````Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
For r = 6 To lr
If Range("C" & r).Value = Range("C1") Then
Range("C1").Copy Range("C" & r)
ElseIf Range("C" & r).Value = Range("C2") Then
Range("C2").Copy Range("C" & r)
ElseIf Range("C" & r).Value = Range("C3") Then
Range("C3").Copy Range("C" & r)
ElseIf Range("C" & r).Value = Range("C4") Then
Range("C4").Copy Range("C" & r)
End If
Next r
End Sub``````
Thank you Michael M, working as treat all perfect!!

Regards,
Moti

#### motilulla

##### Well-known Member
Here is a tested-to-work code:
Code:
``````Sub ColorCell()
For Each cll In Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row)
With cll
Select Case .Value
Case "A - A": .Interior.Color = vbGreen
Case "C - C": .Interior.Color = vbRed
Case "A - B": .Interior.Color = vbGreen
.Characters(Start:=5, Length:=1).Font.Color = vbRed
Case "C - A": .Interior.Color = vbRed
.Characters(Start:=1, Length:=1).Font.Color = vbWhite
.Characters(Start:=5, Length:=1).Font.Color = vbGreen
End Select
End With
Next cll
End Sub``````
Thank you Tetra201, working fine only C-C font is Black instead White

Regards,
Moti

#### Tetra201

##### MrExcel MVP
Thank you Tetra201, working fine only C-C font is Black instead White

Regards,
Moti
You are welcome.

The "C - C" font color was black (automatic) on my test sheet,
but here is a modified version that explicitly sets the font color to black:
Code:
``````Sub ColorCell()
For Each cll In Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row)
With cll
.Font.Color = vbBlack
Select Case .Value
Case "A - A": .Interior.Color = vbGreen
Case "C - C": .Interior.Color = vbRed
Case "A - B": .Interior.Color = vbGreen
.Characters(Start:=5, Length:=1).Font.Color = vbRed
Case "C - A": .Interior.Color = vbRed
.Characters(Start:=1, Length:=1).Font.Color = vbWhite
.Characters(Start:=5, Length:=1).Font.Color = vbGreen
End Select
End With
Next cll
End Sub``````

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,510
Messages
5,854,145
Members
431,620
Latest member
imsyun

### 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.

### Which adblocker are you using?

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

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