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.
Related:

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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023
May 3, 2022 at 09:27 AM
Sure, thanks!!
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
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