Best way to change the value of a cell if another cell has a specific value
i.e.I want to change cell F11 to No if F12 = Yes and also the other way around F12 to No if F11 = Yes, so only 1 of these cells can say Yes, but they both can say No.
I would like to change the greater than number to the value in cell 'I11' in sheet 'Linear Programming Data' and the less than number to the value in cell 'I12' in sheet 'Linear Programming Data'. The code I am using was done by recording a macro since I don't know how to code in vba.
Code:
Sub Results2()
' Results2 Macro
' copy table filter power by greater than and less than
Sheets("Finalizing Results 2").Select
Cells.Select
[Code] .......
I've done the following:
Sub BrownBH()
If Range("Brown!B4:B31") = X Then
Range("C4").Value = [#A]
Else
Range("C4").Value = NT
End If
End Sub
However, this doesn't even work.
When somebody enters an X in a specific cell on one worksheet, it's supposed to change the value to A of a specific cell in a different worksheet. Sounds simple enough...but...
I have a spreadsheet which has a version number in it on a given cell. This cell is protected as is the spreadsheet.
I want to create a macro that will target the given cell and ask user to input new value and then change the cell value.
This macro will be attached to a button.
I already know how to disable and enable spreadsheet protection but have got no clue as to go about the rest.
I would like to change a combo box back to a specific value, based on a change in C4.
So no matter what happens, if c4 changes at all, the value in the combo box gets reset....
I need in some way to make the range variable, because it can change. I need only the first range to be different.
Code:
x = .Range("a4:a27").CurrentRegion.Value
A4 have to be instead the cell where Denmark typed. Column A
And
A27 have to be the lasted used cell in column B
But after the row where it have found Denmark.
Please have a look.
Below code is from AB33, But this is not a cross post.
Different question
Code:
Sub copyp()
' Denmark
Application.ScreenUpdating = 0
Dim cell As Range, i&, dic
With Worksheets("Middle Result")
x = .Range("a4:a27").CurrentRegion.Value
[Code] .......
I know how to set a conditional for a cell to change a color when i apply a specific value, but how about if i want to set E2 cell to change to green when i place a 'X' on F2?
Pretty much I want 2 columns that say Yes and the Other No. When i place a X on Yes that other cell turns green, if i place a X on No that other cell turns red.
My boss wants a spreadsheet that has multiple buttons on it that will change the background color of a specific cell to four different colors. Example:
Text written in Cell B3, Button in Cell A3 that will change the Background color of Cell B3 to either Green, Yellow, Red or Blue.
There will be a lot of buttons on this worksheet following the same format as above. I don't want to change the value of what is in the cell, just the background color.
I'm trying to have a macro run to search a specific column (S) for a particular text value "y" and if true it will change the font to bold and the color to red for the entire row of the cell that contains the "y". the column is part of a data table that is constantly refreshed not sure if that makes a difference or not.
View 2 Replies View RelatedI have a code and I want to run this macro whan a specific cell change (which has sum formula) and this code also has some calculation. And I m not understanding to overcome this problem through Calculate event.
View 2 Replies View RelatedI need to conduct keyword searches in excel and color the keywords a different color without changing the font color of the entire cell. now, i found some code from another thread that does this, but i cant seem to get it to work with more than 2 keywords. i need fine-tuning the provided code to do what i need it to do.
[URL]
the VBA code i used was this:
[Code].....
I was playing around with the example, the Cat_Mouse.xlsm, and when i modified the 'myList' and 'myColor' arrays, it does not work.
Cat_Mouse.xlsm
For example, I modified the code to add the word bat:
[Code] .....
When i run the macros, the word "Bat" does not become colored red. interestingly enough, when i substitute the word "bat" for the word "hat" in the "myLIST array, the word "hat" does change to the red font.
I am looking to use this code to address keywords in my excel file by coloring all key words red and i have more than 10 key words.
I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)
second question
I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)
I have below formula.i need to change the cell address AK in the below formula on the value change in the A3.
=SUMIF(Working!$D$6:$D$3000,Report!B3,Working!$AK$6:$AK$3000)
For example if A3 is having value AZ i need the formula changed automatically as below
=SUMIF(Working!$D$6:$D$3000,Report!B3,Working!$AZ$6:$AZ$3000)
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select
Is there a way to format cells to where - when you type in a certain person's first name, it will insert something entirely different?
This has seemed to have happened on a computer at work.
In Excel, when I type in "Martha" then tab, or otherwise leave that cell, Martha changes to "Cool"
If I type in anyone elses name - it stays that name. But if I type in "Martha" .. it changes to "Cool" with the bullet and the word Cool.
Looking to have a macro call a subroutine every time it finds a cell meeting specific criteria.
Code in plain english would look like this:
For EACH cell in range A1:BZ500 meeting the following criteria:
Cell value is a date
AND
Cell's date is at least a week or more in the future
AND
Cell background (Fill) = RGB color code: (R:191 G:191 B:191)
DO the following:
Call repeatingsub
I paste new data into a sheet of a monthly report I prepare. For this sheet, the # of data rows change (and is unpredictable) every month. I need the value inside a specific cell that dynamcially moves up and down based on the # of rows for that month (because it's below the rows of data).
So I made a formula to identify the exact cell # every month.
Example:
This month the exact cell is F255 in the "Refi" sheet.
So my formula in the "Summary" sheet cell A1 first finds the cell row # only (255) and since it's always column F, in B1 I have
VB:
="F" & (A1)
This outputs "F255" in B1, successfully identifying the target cell.
Now how do I write a formula in C1 to grab the value from whatever cell is named in B1. (For this month, the value in cell F255 from the "Refi" sheet)
I have a form that loads and depending on the word selected in the drop down the following code loops through cells Q2:AC2 until it finds the word in one of those cells (the word will always be in one of the cells)
Code:
For Each c In Range("Q2:AC2").Cells
If c = period Then
c.Select
[Code]...
The active cell it finds will always change, i know I need something to code the active cell back but I don't know what it should be.
I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
What I'm trying to do is, create a formula that works like vlookup, except I want to return the value of the cell that is a specified number of cells below the looked-up data in a single column array. So, let's say cell A1 reads "Bacon". In B1, I'd like to put a formula which searches the array for the cell that contains "Bacon" and then returns the value of the cell underneath, let's say the value is "Eggs". Then in C1, I'd like to put another formula which also looks up "Bacon" in the array, then returns the value of the cell that is down two cells from the cell that contains "Bacon", let's say the value is "Milk". And then in D1, a formula that returns the value 3 cells below "Bacon", and so on. All the values here will be text, and not sorted in any specific order. I'll be using Excel 2010.
Please take a look at the attached sheet if it clarifies things.
In my excel workbook, I have a customer table and invoice sheet among many others.
As part of my system, there is functionality to grant new customers with a discount on the first purchase. Within my customer table, the last two columns are "Number of Purchases" and "Customer Type" (either single/multiple depending on no. of purchases), which are then used to determine whether the discount is valid or not on the invoice.
Once an invoice has been created, archived and refreshed with the customer selected (via a Customer ID), I would like some code to auto increment the number of purchases on the Customer table for that specific customer to +1.
For example, customer called Bob (Bob-1);
Number of purchases = 1
Customer Type = Single
Discount = Yes
Invoice then created for Bob (using Bob-1 as the unique value), sent off and refreshed. New figures should be:
Number of purchases = 2
Customer Type = Multiple (can be achieved by using IF statement on No. of purchases)
Discount = No
I would like to know how to use a VBA code to insert today's date into a specific cell (B9) when any cell in column B (B2:B8) has changed.
View 3 Replies View RelatedI'm trying to copy data from one excel sheet to another excel sheet. However, the data to be copied is dependent on the 'client name'.
To explain this further, in the first list I have a detailed report on our clients and the services provided to every employee of that company/client.
However, the sheet two only needs the names of the employees that belong to a specific client.
This can be done manually by setting a fliter on the name of the client/company, but I need to be automated. To ensure only that specific company/client company's employee name is copied.
I'm trying to search through multiple worksheets (that are closed) to see if a value in cell B12 (of every worksheet) matches a value in a seperate worksheet (which is in a seperate workbook)
View 14 Replies View RelatedI need to change the color of a specific cell when conditions are met in a different cell and it seems like it may be beyond the scope of simply applying conditional formatting. What vba code would look like in proper syntax to make the following statement true. IF cell value Q4 is >=cell value S4 AND cell value R4 is <100 Then cell Q4 turns red and IF cell value Q4 is >= cell value S4 AND cell value R4 =100 then cell Q4 turns green. it may also be useful to know that the "cell values" in this example will be dates and not straight values. this may be possible to do using conditional formatting, but i cannot find a formula that works.
View 2 Replies View RelatedCan I get a formula for the below specification.
if a cell contains character A THEN multiply by cell B * 0.02,if a cell contains character B THEN multiply by cell B * 0.05,if a cell contains character C THEN multiply by cell B * 0.010
I am trying to create a macro that will allow a user to click a button, enter a text string that will be an email address into a field and have it added to the first empty row in column A or column C on sheet 1. I have not been able to record this with the macro recorder. The input box will be in cell E9 on sheet1.
View 7 Replies View RelatedThis is probably really easy but I don't know what I'm doing wrong.
View 2 Replies View RelatedI want to extract the following details into another collumn (bold ) from data in column B1 into C1. The information i want begins with 3 digits with a '/' then other digits follow.
M / AS308 - :308/65588 POUN
S33 / 33 - :133/01504 TR
NI101 - :101/915635 T
N101 - :101/915635
I have a problem regarding highlighting a specific cell, when i entered a specific date in another cell
Let me explain
If there is a Name, joining date, termination date/resignation date in cell A1 A2 & A3 respectively.
If the name is John, Joining date = 01/01/2012, Resignation Date 06/06/13
I need a formula which highlights the cell A2 plus all those cells which have the same date when i entered a date (i.e 01/01/2012) in another cell.