Bit odd conditional formatting

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
Hello,</SPAN></SPAN>

I want to set conditional formatting in the columns T:AB using bit odd criteria for example... </SPAN></SPAN>
1-Row T4:AB7 has numbers 0, 1 & 2--->Look these numbers in the row D7:Q7 if have match 0 in the row D8:Q8 if yes then fill colour red and font white in the all (0, 1 & 2)</SPAN></SPAN>

2-Row T5:AB5 has numbers 0, 1 & 2--->Look these numbers in the row D8:Q8 if have match 0 in the row D9:Q9 if yes then fill colour red and font white in this case num 1 is in F8 do not have 0 in the F9 so far just fill colour red and font white in the 0 & 2 only.</SPAN></SPAN>

Example data.... </SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1
2
3
4
5n1n2n3n4n5n6n7n8n9n10n11n12n13n14CF1CF2CF3CF4CF5CF6CF7CF8CF9
6
701000111210101012
800100000000210012
91050402101002001245
10210001321011000123
110131207012016210012371216
1202111040002010
Sheet1


Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:
Sort by date Sort by votes
Hm, maybe SpecialCells xlLogical do not exist in Excel 2000.
Let's try using SpecialCells xlTextValues.

Code: 
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1082697b()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1082697-bit-odd-conditional-formatting.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] rng [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], s [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR], vb [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR], vc [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
n = Range([COLOR=brown]"D"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
va = Range([COLOR=brown]"D7:Q"[/COLOR] & n)
[COLOR=Royalblue]Set[/COLOR] rng = Range([COLOR=brown]"T7:AB"[/COLOR] & n - [COLOR=crimson]1[/COLOR])
vb = rng
vc = rng
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]) - [COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]For[/COLOR] k = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(vb, [COLOR=crimson]2[/COLOR])
    
        [COLOR=Royalblue]If[/COLOR] vb(i, k) <> [COLOR=brown]""[/COLOR] [COLOR=Royalblue]Then[/COLOR]
            s = vb(i, k)
            [COLOR=Royalblue]For[/COLOR] j = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]2[/COLOR])
                [COLOR=Royalblue]If[/COLOR] va(i, j) = s [COLOR=Royalblue]And[/COLOR] va(i + [COLOR=crimson]1[/COLOR], j) = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                    [COLOR=Royalblue]If[/COLOR] i [COLOR=Royalblue]Mod[/COLOR] [COLOR=crimson]2[/COLOR] = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                        vb(i, k) = [COLOR=brown]"YES"[/COLOR]
                        [COLOR=Royalblue]Else[/COLOR]
                        vb(i, k) = [COLOR=brown]"#N/A"[/COLOR]
                        [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
                    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
                [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
            [COLOR=Royalblue]Next[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    
    [COLOR=Royalblue]Next[/COLOR]
[COLOR=Royalblue]Next[/COLOR]
rng = vb

    [COLOR=Royalblue]With[/COLOR] rng
        .SpecialCells(xlConstants, xlErrors).Font.Color = vbWhite
        .SpecialCells(xlConstants, xlErrors).Interior.Color = vbRed
        .SpecialCells(xlConstants, xlTextValues).Font.Color = vbWhite
        .SpecialCells(xlConstants, xlTextValues).Interior.Color = vbBlue
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

rng = vc
Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Akuini said:
Hm, maybe SpecialCells xlLogical do not exist in Excel 2000.
Let's try using SpecialCells xlTextValues.

Code: 
[FONT=lucida console][COLOR=royalblue]Sub[/COLOR] a1082697b()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1082697-bit-odd-conditional-formatting.html[/COLOR][/I]
[COLOR=royalblue]Dim[/COLOR] rng [COLOR=royalblue]As[/COLOR] Range
[COLOR=royalblue]Dim[/COLOR] n [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], j [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], k [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], i [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], s [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR]
[COLOR=royalblue]Dim[/COLOR] va [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Variant[/COLOR], vb [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Variant[/COLOR], vc [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Variant[/COLOR]

Application.ScreenUpdating = [COLOR=royalblue]False[/COLOR]
n = Range([COLOR=brown]"D"[/COLOR] & Rows.count).[COLOR=royalblue]End[/COLOR](xlUp).Row
va = Range([COLOR=brown]"D7:Q"[/COLOR] & n)
[COLOR=royalblue]Set[/COLOR] rng = Range([COLOR=brown]"T7:AB"[/COLOR] & n - [COLOR=crimson]1[/COLOR])
vb = rng
vc = rng
[COLOR=royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]) - [COLOR=crimson]1[/COLOR]
    [COLOR=royalblue]For[/COLOR] k = [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(vb, [COLOR=crimson]2[/COLOR])
    
        [COLOR=royalblue]If[/COLOR] vb(i, k) <> [COLOR=brown]""[/COLOR] [COLOR=royalblue]Then[/COLOR]
            s = vb(i, k)
            [COLOR=royalblue]For[/COLOR] j = [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]2[/COLOR])
                [COLOR=royalblue]If[/COLOR] va(i, j) = s [COLOR=royalblue]And[/COLOR] va(i + [COLOR=crimson]1[/COLOR], j) = [COLOR=crimson]0[/COLOR] [COLOR=royalblue]Then[/COLOR]
                    [COLOR=royalblue]If[/COLOR] i [COLOR=royalblue]Mod[/COLOR] [COLOR=crimson]2[/COLOR] = [COLOR=crimson]0[/COLOR] [COLOR=royalblue]Then[/COLOR]
                        vb(i, k) = [COLOR=brown]"YES"[/COLOR]
                        [COLOR=royalblue]Else[/COLOR]
                        vb(i, k) = [COLOR=brown]"#N/A"[/COLOR]
                        [COLOR=royalblue]Exit[/COLOR] [COLOR=royalblue]For[/COLOR]
                    [COLOR=royalblue]End[/COLOR] [COLOR=royalblue]If[/COLOR]
                [COLOR=royalblue]End[/COLOR] [COLOR=royalblue]If[/COLOR]
            [COLOR=royalblue]Next[/COLOR]
        [COLOR=royalblue]End[/COLOR] [COLOR=royalblue]If[/COLOR]
    
    [COLOR=royalblue]Next[/COLOR]
[COLOR=royalblue]Next[/COLOR]
rng = vb

    [COLOR=royalblue]With[/COLOR] rng
        .SpecialCells(xlConstants, xlErrors).Font.Color = vbWhite
        .SpecialCells(xlConstants, xlErrors).Interior.Color = vbRed
        .SpecialCells(xlConstants, xlTextValues).Font.Color = vbWhite
        .SpecialCells(xlConstants, xlTextValues).Interior.Color = vbBlue
    [COLOR=royalblue]End[/COLOR] [COLOR=royalblue]With[/COLOR]

rng = vc
Application.ScreenUpdating = [COLOR=royalblue]True[/COLOR]
[COLOR=royalblue]End[/COLOR] [COLOR=royalblue]Sub[/COLOR][/FONT]
Click to expand...
Akuini, :pray: Just come back to desk computer right now tried modified VBA code you send it is working flawless very correct! And I could save the workbook without any trouble</SPAN></SPAN>

Thank you very much for the help!</SPAN></SPAN>

Query solved!</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>:grin:</SPAN></SPAN>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Advice for conditional formatting not working
Replies
1
Views
333
rlv01
R
K
  • Question Question
Conditional Formating - Dynamic "not between"
Replies
3
Views
559
dreid1011
dreid1011
Cliffork
  • Solved
Conditional Formatting not working properly with AND function
Replies
4
Views
142
Joe4
Joe4
R
  • Question Question
Excel Conditional Formatting Formula to change fill colour based on position of active cell
Replies
2
Views
840
rasinc
R
gtd526
  • Question Question
Conditional Formatting for Matching Odd Rows only
Replies
4
Views
255
gtd526
gtd526

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

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