Bit odd conditional formatting

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,362
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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>:biggrin:</SPAN></SPAN>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Conditional Formating - Dynamic "not between"
Replies
3
Views
292
dreid1011
dreid1011
R
  • Question
Excel Conditional Formatting Formula to change fill colour based on position of active cell
Replies
2
Views
590
rasinc
R
Nuke_It_Newport
  • Solved
Conditional Formatting Rules Not Applying
Replies
1
Views
694
Nuke_It_Newport
Nuke_It_Newport
nicolews
  • Question
Conditional Formatting based on other cell values
Replies
3
Views
843
ERINWILLIS01
E
M
  • Question
[Pls Help!]Grant chart project with conditional formatting
Replies
1
Views
290
etaf
etaf

Forum statistics

Threads
1,215,375
Messages
6,124,578
Members
449,174
Latest member
chandan4057

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