If formula based on cell colour

[TwistedGhost]

hello all,

i am trying to have my document strikethrough a line if a certain cell is black.

does anyone know the easiest way to do this i have tryed to find a if formula that would do it but have not been able to.

an example is below:

Supplier Supplier ID Method Email Contact Name PO # Printed Formatted Emailed Confirmed


i want the formula to check the PO# coloumn and if it is Black then strikthrough the whole line.

is there a way to do this?

[GeneralDisarray]

Ok.

You want to use conditional formatting, but in order to capture the color you need to also involve VBA (see attachment). You have to do this in order to 'expose' the color to the worksheet.

1) Create a function to expose the color to the worksheet. This function will pass the parameter values related to fill color as a string:

Please Login or Register  to view this content.

2) Use that function, colorCheck() for conditional formatting.

Here is the rule for the conditional formatting - you will need to adjust this to match your own sheet:
=(colorCheck($G3)="1-4105-4105200")

How do you get the "1-4105-4105200" value? Just use the function colorCheck to find out what the parameters are for the color you are using. I did this in cell C2 on the attachment, C2 tells me the value i want to match based on the 'colorCheck' for the fill i used in B2.

make sense?

--
edit: notice something odd. the formula works, but only updates when a change happens in the PO# column. So, to utilize this, you would have to double click on a cell in that column (so that you were able to type into the cell) and the press enter.
That action, seems to be needed to trigger the conditional formatting to work.

[TwistedGhost]

That doesn't quite work

however i just thought of another way that i could do it if someone can help with the formula.

since the PO# column is either going to have text or not is there a formula that i can use to have it strikeout the whole line if the box in the PO# column is blank and do nothing if there is text?

[GeneralDisarray]

should have said that in the first place :D

That's much simpler, requires no VBA to work as ISBLANK() is already a worksheet function.

Basically, change the conditional formatting to:

=ISBLANK($G3)

Because a typed value triggers a worksheet change, this will always change when needed. The fill color doesn't qualify as a worksheet change event apparently...

See Attached sheet :D

[TwistedGhost]

only problem that i found is once it strikes though if i add text to that column it wont remove the strikethrough

do you have something for this?

[TwistedGhost]

I fixed it i just added a second condition of =ISTEXT($F1) to apply no strikethrough

i am pretty sure that last issue that i have is i create new tabs from a template sheet with macros is there a way to have it apply the same conditional formatting to all created tabs?

[TwistedGhost]

i fixed that one 2 by applying the conditional formatting to the template tab
thank you so much for the help

[TwistedGhost]

i Know this is dumb question but how do i mark as SOLVED LOL

[Harribone]

Welcome to the forum!

At the top of this page click Thread Tools>marked as solved.
Also you can click on the star icon in the left corner of users posts to give rep/feedback to them when they are helpful like GeneralDisarray has been (if you haven't already/feel that user deserves credit).

[GeneralDisarray]

Glad to here this solution worked for you, but you don't need a second condition. (this comment assumes you are using the second example i posted and not the first).

1) conditional formatting evaluates on a change. When you add text to a blank cell (and see the lines through the text) the lines will go away when you press enter. Conditional fomatting doesn't re-evaluate until the change is completed.

2) if you never want the lines on the PO# column, just remove that column from the Apply To range for that rule. You can just include the other columns.

example attached

[TwistedGhost]

ok i have fixed all issue but 1

when my macro runs to make a new tab it copies everything but doesnt keep the cell sizes so information is hidden and checkboxs are squished together

here is my new sheet macro

Please Login or Register  to view this content.

[GeneralDisarray]

It's because you are using "Sheets.add' to make the sheet and are then copying the sheets contents over.

You would be better off to do it with a .copy that way everything gets copied over to the new sheet.

I can't run and debug the code i have below, but if memory serves this will do what you need. The new sheet will be identical to the original sheet, includes formats, values, formulas, named ranges... etc

Please Login or Register  to view this content.

--edit--

ALso, i used two worksheet variables. That's optional i suppose but i never like to just reference the 'active' sheet... seems to lead to problems. the only thing to know about using worksheet variables is they have to be "set" before they are referenced. they work like pointers to the sheets

also, i accidently typed "thisworksheet" where i meant "thisworkbook" -- i fixed it but if you already tried the code it would fail there.

[TwistedGhost]

OK so i think my last issue is in the macroing.

what i want is with the check boxes i want it to change things when the check box is checked

first off look at the attached example please

Macro Help.xlsx

When i enter a number in the po# column i want it to create a check box in the printed column and when the printed column is checked i want it to first check the color of the method cell and if yellow create the formated check box in the formatted column if any other color leave formatted column blank. If a formatted check box was created when it is checked have it create the emailed check box other wise if no formatted check box is created then create a check box in the emailed column and when the emailed box is checked i want it to change the cell color of the confirmed box to red until the check box for confirmed is checked then turn it to green.

please help me if this is something that can be done.

[GeneralDisarray]

ok..

can i suggest an easier alternative? why not allow for Yes / No on those columns only. I don't think the check boxes are really tied to the cell, so i'm not sure how you would pull off what you are describing but i'm sure it's possible somehow.

SOunds like you just want people to fill in more information in a sequential way. See the attachement. You can have the parts the user needs to fill in highlight when they should be filling them in.

[TwistedGhost]

thats not quite what i need it has to be check boxs thank you though

[GeneralDisarray]

Hmm.. start a new thread for this one please. The problem has totally changed and this 'checkboxes' problem needs a title to reflect what you need.

I think we answered the first question, please mark this thread as solved and start a new one.


To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save