VBA script to change cell color if text has strikethrough

VBA script to change cell color if text has strikethrough

Post by Michael Landr » Thu, 07 Jun 2012 05:33:29



I am working on a spreadsheet that contains several columns with due dates.
 I have conditional formatting rules that change the cell color as a due
date gets closer to today's date.
<15 days = yellow
<10 days = orange
<5 days = red

Once an activity has been completed, the user formats the date with
strikethrough. At this point, I would like to change the cell color back to
"none" so the user doesn't have red cells showing for activities that have
already been completed.
It appears that I will need a vba script to do this, but I have no idea how
to get started.

Any assistance would be greatly appreciated!

 
 
 

VBA script to change cell color if text has strikethrough

Post by NOORAIN ANSAR » Thu, 07 Jun 2012 11:33:52


Dear Michael,

Please share a sample workbook with Group.

--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com


> I am working on a spreadsheet that contains several columns with due
> dates.  I have conditional formatting rules that change the cell color as a
> due date gets closer to today's date.
> <15 days = yellow
> <10 days = orange
> <5 days = red

> Once an activity has been completed, the user formats the date with
> strikethrough. At this point, I would like to change the cell color back to
> "none" so the user doesn't have red cells showing for activities that have
> already been completed.
> It appears that I will need a vba script to do this, but I have no idea
> how to get started.

> Any assistance would be greatly appreciated!

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to



 
 
 

VBA script to change cell color if text has strikethrough

Post by David Grugeo » Thu, 07 Jun 2012 15:37:42


There are problems with this as the conditional formatting overrides any
locally applied formatting so the macro would need to exclude that cell
from the conditional formatting - not easy and not advisable.  Conditional
formats can get confused enough without any help.

I would suggest you have a column called Status which has either
"Uncompleted" or "Completed" you could usefully limit it to these with a
validation dropdown.  You could include other statuses such as "Working",
"Not Started" if you wanted to.  Lets call this Column Z

Now: in the cell where the conditional formatting is you include, as your
first condition =Z1="Completed" and a format of No fill and Strikethrough.

HTH

David


> I am working on a spreadsheet that contains several columns with due
> dates.  I have conditional formatting rules that change the cell color as a
> due date gets closer to today's date.
> <15 days = yellow
> <10 days = orange
> <5 days = red

> Once an activity has been completed, the user formats the date with
> strikethrough. At this point, I would like to change the cell color back to
> "none" so the user doesn't have red cells showing for activities that have
> already been completed.
> It appears that I will need a vba script to do this, but I have no idea
> how to get started.

> Any assistance would be greatly appreciated!

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


--
David Grugeon
 
 
 

VBA script to change cell color if text has strikethrough

Post by Rajan_Verm » Thu, 07 Jun 2012 23:40:34


Hi,

You just need to modify your Formula in CF. you can take any cell on
worksheet as  ON OFF Flag . E.g

If I am taking range A1 then your formula will be :

= and($A$1<>1,Day<15)  to make format yellow

When the user will Enter 1 in A1 , Condition in the formula will be false
and CF will not apply on the Range, I think we do not have any option to
Disable CF except delete all conditions.

See the attached Sheet

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of David Grugeon
Sent: 06 June 2012 12:08

Subject: Re: $$Excel-Macros$$ VBA script to change cell color if text has
strikethrough

There are problems with this as the conditional formatting overrides any
locally applied formatting so the macro would need to exclude that cell from
the conditional formatting - not easy and not advisable.  Conditional
formats can get confused enough without any help.

I would suggest you have a column called Status which has either
"Uncompleted" or "Completed" you could usefully limit it to these with a
validation dropdown.  You could include other statuses such as "Working",
"Not Started" if you wanted to.  Lets call this Column Z

Now: in the cell where the conditional formatting is you include, as your
first condition =Z1="Completed" and a format of No fill and Strikethrough.

HTH

David


I am working on a spreadsheet that contains several columns with due dates.
I have conditional formatting rules that change the cell color as a due date
gets closer to today's date.

<15 days = yellow

<10 days = orange

<5 days = red

Once an activity has been completed, the user formats the date with
strikethrough. At this point, I would like to change the cell color back to
"none" so the user doesn't have red cells showing for activities that have
already been completed.

It appears that I will need a vba script to do this, but I have no idea how
to get started.

Any assistance would be greatly appreciated!

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to


--
David Grugeon

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

  Book2.xlsx
13K Download
 
 
 

1. Change text color in cell based on text

Hello,
Can anyone tell me how to write a macro or just set a format for a cell
so that the text color changes based on the text in the cell.  In
particular, the cell will hold one of four letters, A, B, C, or D.  I
want the color of the text to change based on a comparison to another
cell.

For example, suppose cell A1 contains a B.  In cell A2, I will have any
one of the four letters show up at random.  I want the color of the text
of A2 to turn to red if it matches what is in A1.  If it does not match,
then it can just stay black.

Any help would be appreciated.

Steve McCullough

2. Can single sub-directory be uncompressed under Stacker 4.1 ?

3. Lotus 123 -97 Change text colour based on cell value

4. CEBus Thermostat

5. Function to change text in cell (A2) to cell address (=A2)

6. source for geared motors.

7. Excel Macro: How change "Text" to "Text (6)" in multiple cells

8. H8/300H evaluation systems

9. Getting text entry in one cell to return text entry in adjoining cell

10. Change in one Cell should lead to change in all related cells

11. VBA code for returning text from a cell comment in EXCEL

12. Excel 97 - strikethrough text

13. Working w\text in cells in VBA