Previous Topic Next Topic

Conditional format B1 based on a specific word in A1 & calc

Sorrow81
Posts
3
Registration date
Thursday April 28, 2022
Status
Member
Last seen
May 3, 2022
 - Updated on Apr 28, 2022 at 04:00 AM
Sorrow81
Posts
3
Registration date
Thursday April 28, 2022
Status
Member
Last seen
May 3, 2022
 - May 3, 2022 at 09:27 AM
Hello,

I would like to check how to use the conditional formatting to get a colored cell in B1 containing numbers (based on a "INC VAL" in Cell A1 sentence) and when this is colored, B1 will automatically do the calculation of the updated values

e.g. if A1 contains "INC VAL" in the cell sentencing, then B1 will be colored and B1 will take the number in its cell and do the a 10% multiplication. So it can be A1, A3, A7 containing "INC VAL" which then will be color and calculated, the rest NOT containing "INC VAL" will not be color nor calculated.

I am doing this as a table (A1, A2, A3 .... so forth vs B1, B2, B3 ,.... so forth), thank you in advance for the advice on how to go about it.

2 replies

Answer 1 / 2
Sorrow81
Posts
3
Registration date
Thursday April 28, 2022
Status
Member
Last seen
May 3, 2022
 1
Apr 30, 2022 at 01:00 AM
Hi Trowa, noted and thanks for your response, I'll look into your formula, yes it should B1*1.1, you're right. I also note your respond days, appreciate and thanks once again. :)

i'll try to see how to move about without using VBA (if possible), I don't think i even know how to use it haha.
1
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 510
May 2, 2022 at 11:31 AM
Well, if you choose to use VBA, we'll be here to help you with that as well!
0
Sorrow81
Posts
3
Registration date
Thursday April 28, 2022
Status
Member
Last seen
May 3, 2022
 1 > TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
May 3, 2022 at 09:27 AM
Sure, thanks!!
0
Answer 2 / 2
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 510
Updated on Apr 28, 2022 at 11:46 AM
Hi Sorrow81,

First the conditional formatting:
Select Column B, go to conditional formatting and choose to use a formula:
=ISERROR(FIND("INC VAL",A1))=FALSE

For the calculation:
Since it's not usual nor advised to put both a value and formula in the same cell; you have 2 options.
1. The default way: Use Column C for the formula: =IF(ISERROR(FIND("INC VAL",A1))=FALSE,(B1*0.1)*B1,"")
2. Column B contains both a number (I chose 999) and the calculation: ="999 " & IF(ISERROR(FIND("INC VAL",A1))=FALSE,(999*0.1)*999,"")

NOTE: It is not possible to place a number in a cell and let a formula replace that value. It can be done using VBA.
NOTE2: For calculation you say '10% multiplication'. Lets say the value is 100, then 10% would be 10 and the multiplication of that would be 1000. Not sure if that is what you meant. If you meant 10% addition then the part of the formula (could also be a translation thing as English is not my native language):
(B1*0.1)*B1
should be:
B1*1.1

Best regards,
Trowa
0