VBA and conditional formatting

[Sjryals]

I have created a form with a macro to sort a list into a given order preferance. There is also a checkbox function that highlights a group of cells in a row based on true/false checks. When i sort - the check boxes move but conditional formatting does not. Thus you could check b3 and it highlights columns in row 6 rather than row 3 based on the reorder. How can i make them all follow each other?

Help please!

 

[mart37]

Have you link your checkboxes with cells in the table?
Then you can beter place them outside the table.
When you sort then the references to the linked cells remain the same.

 

[Sjryals]

Have you link your checkboxes with cells in the table?
Then you can beter place them outside the table.
When you sort then the references to the linked cells remain the same.

I have tried that it did not seem to make a difference. Let me play with that some more.

 

[Sjryals]

I think part of the issues is the conditional formatting is an absolute reference and I cannot figure out how to get that to follow as you sort...so if I move item 1 to place 3 then it will highlight place 1 when I click 3.

 

[mart37]

	A	B	C	D	E	F
Month	Highlight	Sortorder	Selectionbox
okt	FALSE
nov	FALSE
TRUE
may	FALSE
TRUE
TRUE
TRUE
dec	FALSE
TRUE
TRUE
apr	FALSE

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #ffff00"]mrt[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #ffff00"]jun[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #ffff00"]jan[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #ffff00"]feb[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #ffff00"]dec[/TD]

[TD="align: right"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #ffff00"]aug[/TD]

[TD="align: right"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

</tbody>

Spreadsheet Formulas

Cell Formula CF =IF(AND($B2,$K$2),TRUE,FALSE) <tbody> </tbody>

<tbody>
</tbody>

Selectionbox in D2 is linked to K2
Select A2:A12
CF --> Formula --> see CF

B2 can you replace with your true/false check

Excel tables to the web >> Excel Jeanie HTML 4

 

[Sjryals]

	A	B	C	D	E	F
Month	Highlight	Sortorder	Selectionbox
okt	FALSE
nov	FALSE
TRUE
may	FALSE
TRUE
TRUE
TRUE
dec	FALSE
TRUE
TRUE
apr	FALSE

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #ffff00"]mrt[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #ffff00"]jun[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #ffff00"]jan[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #ffff00"]feb[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #ffff00"]dec[/TD]

[TD="align: right"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #ffff00"]aug[/TD]

[TD="align: right"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

</tbody>

Spreadsheet Formulas

Cell Formula CF =IF(AND($B2,$K$2),TRUE,FALSE) <tbody> </tbody>

<tbody>
</tbody>

Selectionbox in D2 is linked to K2
Select A2:A12
CF --> Formula --> see CF

B2 can you replace with your true/false check

Excel tables to the web >> Excel Jeanie HTML 4

When I do this, the check box does not follow the conditional formatting.
[TABLE="width: 1479"]
<colgroup><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Sjryals]

When I do this, the check box does not follow the conditional formatting.
[TABLE="width: 1479"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

if you sort your example, does the formatting move with it?