Fill D1:D4 format all the way below

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,353
Office Version
  1. 2010
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
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"
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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