I have a spread sheet which contains a list of names and 2 columns of data for each month of the year. The top cells of each column are merged and contain the month i.e. Sep-10. The data has to be inputted manually, only if a name is in column A and so I would like the relevant columns to be shaded which shows data is to be inputted.

I have used this formula in the condittional formatting

=AND($A6>0,AND(C$4

I have used this formula in the condittional formatting

=AND($A6>0,AND(C$4

- Conditional format formula based on two cells
- Excel2010 Cond formatting by cell value but rules value based on value in other cell
- Set Color of Cell Background Based on value in another cell
- Lock a Cell value based on value in another cell - (W/O involving VBA Code or Macro)
- Excel Locking cells based on value in another cell
- Excel Locking cells based on value in another cell
- Automatically adjust shape size based on value in a cell
- Conditional Formatting based on values in multiple cells
- Lookup a value based on values in two cells
- Select cell value based on value in adjacent cell
- Select cell value based on value in adjacent cell
- VBA insert formula based on value in another column and loop rows
- Formula to set font color based on value in other cell
- VBA method of copying values from RAND() to a range based on values in another range
- Conditional Formatting - Formula based
- Conditional Formatting based on Text in the cell
- Change the tab color based on value in a cell? Is it possible?
- Conditional formating based on text in adjacent cell
- Conditional formatting columns based on criteria of 2 cells
- Input data or Lookup data based on value in another cell
- How to populate column with formula based on value in cell
- Conditional formatting of entire rows:value of 1 cell
- Conditionally lock cells based on value in input cell
- Vba to set range cell values based on value in two other cells

I want to create a conditional format formula which is based on values in 2 cells.

Example:

Cell B1 = 100

Cell C1 = 50

I want a formule for cell A1 which turns red for exmaple when B1 is higher then 85 and Cell C1 is lower then 85. Only in this case the conditional format should work.

I guess it's very easy but I can't get it done so I guess I make a mistake in the formula with connected the two conditions. Any idea's?

I'm using the english version of office 2003.

Thanks a lot!

in one column i have values and they should be formatted based on the value in that cell but the top and down values of the rule should be based on value in other cell (categ1, categ2, categ3) and the category can be changed manually (cell value which decides the top and bottom values)

For example:

value in cell A1 is 900€ and in cell B1 is category1 (<500 red, >1000 green so so this should be yellow). BUT if i change manually the category in B1 to kat2 (where <1000 red...) the conditional formatting should now change the cell red because the category value in B1 changed.

Can this be done in excel2010 and how?

Hi,

Here is my example. I need to set the fill background of cell b4 based on the value that it is c4. Obviously you can't do this using conditional formatting because it would only work for c4. It looks like this:

b4 - blank cell

c4 - "complete"

I need b4 to be green if c4 = complete and i need it to be red if the cell is equal to something else.

Thanks

This is James from Singapore.

Sincerely need your help here as I have been trying to figure out how to solve this problem.

I am doing an assignment for an accounting project.

I have attached the project sample in this thread:

Attachment content and MY QUESTION:

1) Column B shows the calculated ratios

2) Column D and E allow user to "copy data" from Column B

3) To avoid the data in Column D and E from being changed accidentally, I have created another drop down selection with 2 options " to protect data now" and "let it change"

4) the thing is, I don't know how to lock the cell value if the user has selected "to protect data now"

Can any kind souls here advise?

Thanks.

Question on Locking Cell based on value in another cell.xlsx

My code locks another cell based on value in another cell. I got it working just for 1 row but i would like to do it for the subsequent rows (E10, E11, E12, E13...)... any help??

Code:

Private Sub Worksheet_Change(ByVal Target As Range) If [E9] = "A" Then ActiveSheet.Unprotect ("") ActiveSheet.Range("G9:K9").Locked = True ActiveSheet.Protect ("") Else ActiveSheet.Unprotect ("") ActiveSheet.Range("G9:K9").Locked = False ActiveSheet.Protect ("") End If End Sub

Thanks in advance!

My code locks another cell based on value in another cell. I got it working

just for 1 row but i would like to do it for the subsequent rows (E10, E11,

E12, E13...)... any help??

Private Sub Worksheet_Change(ByVal Target As Range)

If [E9] = "A" Then

ActiveSheet.Unprotect ("")

ActiveSheet.Range("G9:K9").Locked = True

ActiveSheet.Protect ("")

Else

ActiveSheet.Unprotect ("")

ActiveSheet.Range("G9:K9").Locked = False

ActiveSheet.Protect ("")

End If

End Sub

Thanks in advance

I've been wrestling with what is probably a simple problem. I have a map of the US on one worksheet with shapes (circles) over locations where our company has resources. I've put in a button on that sheet that toggles the shape on or off using the .visible = true or .visible = false properties. This button also has a caption that toggles as well. However, I also want to dictate the size of those shapes based on values entered in another worksheet in the same workbook. I've tried and just can't get it to work. Here's the kind of code I am using to toggle the visibility. I'd like to insert code into this event that will also adjust the circle size based on value in a cell. Thanks!

Private Sub CommandButton5_Click() If CommandButton5.Caption = "Hide" Then CommandButton5.Caption = "Show" ActiveSheet.Shapes("Oval 1_LosAngeles").Visible = False Else If CommandButton5.Caption = "Show" Then CommandButton5.Caption = "Hide" ActiveSheet.Shapes("Oval 1_LosAngeles").Visible = True End If End If End Sub

a1 and a2. both have to number 1 entered. So i would like c1 to be green, BUT only when both cells have 1 entered. two conditions does not work, because it only requires one cell to have 1, to turn c1 green. can anyone help?

Thanks

How do I lookup a value based on values in two other cells?

I'll try to explain what i mean. My worksheet looks like this:

Column A contains values for "Type" (A1="Type", A2=1, A3=2)

Column B contains values for "Big" (B1="Big", B2=33, B3=22)

Column C contains values for "Medium" (C1="Medium", C2=22, C3=15)

Column D contains values for "Small" (D1="Small", D2=11, D3=9)

This means that Type 1 have the value 22 under "Medium" and Type 2 have the value 22 under "Big"

Lets say I input the value 1 in cell B7 and value 22 in cell B8.

Now I would like a formula in cell B9 that would return "Medium" because value 22 is found in column "Medium" for "type" 1.

And if I change the value in B7 to 2, then B9 should return "Big" because the value 22 is found in column "Big" for "Type" 2.

What would the formula be for cell B9? It should return either "Big, "Medium" or "Small" depending on the values in B7 and B8.

Thanks.

usage.

Here's the setup:

I have a column P in which conditional data is entered (e.g., if a person

used Holiday accrual time, they enter code "60"; this field may or may not be

populated, and may use one of 3-4 different codes). The cells in this

column are not contiguous, but broken up by merged cells.

The next coloumn, Q, holds the actual hours of accruals used (general

formatting). The cells in this column are not contiguous, but broken up by

merged cells, matching column P.

Way down at the bottom of the timesheet, I need to calculate the number of

hours of accruals used based on what code is entered in column P. So here's

what I need to figure out how to calculate:

If any cell in column P (P4-43, skipping the merged cells) holds the code

"60", then find the corresponding value in the adjacent cell in column Q,

then sum the values in the cells that meet the criteria and display it in

cell N49.

I don't know if the actual cell references help you or not, but I'm

hoping they will. I'm frustrated no end. I know this can be done, in

theory, because it was done in another application - Word Perfect (more

likely dropped in from Quattro Pro). I just don't know if it can be done in

Excel, or how. The table in Word Perfect is protected and I can't open to

see the formulas and references that were used, and the person who created it

is long gone from our office. I hope someone can help!

usage.

Here's the setup:

I have a column P in which conditional data is entered (e.g., if a person

used Holiday accrual time, they enter code "60"; this field may or may not be

populated, and may use one of 3-4 different codes). The cells in this

column are not contiguous, but broken up by merged cells.

The next coloumn, Q, holds the actual hours of accruals used (general

formatting). The cells in this column are not contiguous, but broken up by

merged cells, matching column P.

Way down at the bottom of the timesheet, I need to calculate the number of

hours of accruals used based on what code is entered in column P. So here's

what I need to figure out how to calculate:

If any cell in column P (P4-43, skipping the merged cells) holds the code

"60", then find the corresponding value in the adjacent cell in column Q,

then sum the values in the cells that meet the criteria and display it in

cell N49.

I don't know if the actual cell references help you or not, but I'm

hoping they will. I'm frustrated no end. I know this can be done, in

theory, because it was done in another application - Word Perfect (more

likely dropped in from Quattro Pro). I just don't know if it can be done in

Excel, or how. The table in Word Perfect is protected and I can't open to

see the formulas and references that were used, and the person who created it

is long gone from our office. I hope someone can help!

I am a bit of a novice with VBA and would appreciate some help please.

I am trying to write a macro to populate column B with a formula based on the value in column C, then loop through until last row. I've modified some code I found but can't get it to work properly. Basically, if the letter "P" appears in a cell in column C a formula is to be placed in column B of the same row, but if the letter "F" appears then an alternative formula is to be entered. Also, if the cell in column C is blank then the code should skip to the next row without altering that row. Hope this makes sense!

Sample code follows and test file is attached.

VB:Thanks for any help.CalcColB() Dim x As Integer For x = 3 To Cells(Rows.Count, "A").End(xlUp).Row Select Case Cells(x, 3) Case Is = P Cells(x, 2) = "=RC[-1]*100" Case Is = F Cells(x, 2) = "=RC[-1]*1000" Case Else Cells(x, 2) = "=""""" End Select Next x End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

please advise? thanks

I need the "Chosen SPP" field to be locked in once data is added to the next column. For example, once the user has acheived a result in C5, that result will be locked into C5. The user will then move on to Column D, select their options and acheive a result in D5. That result will be lock into D5. The user will then move on to Column E... etc. etc.

Originally Posted by ConneXionLost The RAND() function will give you a new set of data every time the spreadsheet recalculates. As long as you're relying on the RAND() function as the source of your data, it will place the problem of "locking in" the selected values well beyond the capabilities of any LOOKUP formula or conditional format solution. Especially if you intend to do this with multiple examples.

I recommend you begin a new thread with the suggested title of: "VBA method of copying values from RAND() to a range based on values in another range".

Hopefully one of the VBA experts here can help you.

Good luck,

know.

I am interested in using conditional formatting, formula based, where

using a variable length sheet, compare column F values to adjacent

column G values - if F is greater than G, color H red, if F is less

than G color H blue.

I wasn't sure how to make it work for the whole column.

Ideas?

Ben/ND

This may be helpful to youl.

I had a task of grading employees on various skill sets, and for each employee there were 10 criteria and each going into "Average, Good, Excellent and Outstanding"

Now to show the performance of each on each sacle, the numbers were not the best way, so we desided to have a colour scheme. The Cells calculated the grade based on marksings in each cell.

="Total="&C10&" "&IF(C10

Thanks!

I have been having some trouble with conditional formatting.

To keep it to the point:

- I have 2 Columns, A& B... one with GL accounts and the other with balances from that account

- The GL accounts are labeled such that the third digit changes into either "1" or "2" ... so like 1-Series and 2-series ... for example, 10123, 10223 or 10113, 10213

- I want to create a conditional formatting formula such that if Column A has a GL with the "1"-series and Column B has a Negative Balance

My issue is this: If the selected value is "New", I wish the user to input data into three other columns (eg, price, description, weight). If the value is "Stock", I wish to look up values for those three columns based on the item name with the data held in another worksheet. That is, I want to conditionally do a VLOOKUP or have the user fill in a cell, based on the value "New" or "stock (data value in another cell).

What is a good way to do this?

laboratory instruments. Data Initially got dumped into cell i10.

Columns a-f contain various formulas (which are mostly used as lookups

for other workbooks) that are dependant on whether there is a value in

the cell i10. Each Column has a different formula.

At the moment, everytime i enter in new data, for eg in cell i11, i12,

i13 etc, select the range from a-f then I drag the fill handle

downwards to populate the new rows so that my formulas are repeated.

(I elected not to populate the formula in anticipation for the growth

in data to row 65000 as the workbook size became to big and

performance was slow).

If there a way to populate the relevant row in colum a-f by copying

the formulas down automaically?

Thanks

Cameron

1 cell in that row. For example, I have a list of clients at our agency, but

when they are terminated I would like to be able to highlight the line in

gray instead of just deleting the record.

I am trying to lock cells fro accepting input based on the value in another cell.

Her it is...

I would like to block data from being entered in K16:K30 if K10 > 0.

in conjuction with the above, What would really be great is to have k10 set to '0' based on new entry into D5....

Thanks to all in advance...

For example, if column A (type date) is NULL, set column C (type general) to "YES"

OR

if column A (type date) is NOT NULL AND column B (type general) is "YES", set column C (type general) to "YES"

Thanks for all your help.