Free Microsoft Excel 2013 Quick Reference

Conditional Format Formula Based On Value in merged cell

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


Post your answer or comment

comments powered by Disqus
Hi

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!

So my problem:

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?

Set Color of Cell Background Based on value in another cell::

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

Hi

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

Hi all

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!

Hi all

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

Hi everyone,

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


Im trying to colour a cell based on data in two cells. E.g.

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

Hi.
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.

I'm creating a timesheet and running into some trouble calculating total time
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'm creating a timesheet and running into some trouble calculating total time
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!

Hello,

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:
	
 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for any help.

Hi - I need a formula that looks at a value within a column, and based on the content of that cell set the font color of a value in another cell. so example, I have a list of names in column A, name1, name2, name3. If any of the names = John Doe, then I want cell B1 to be formatted to display in bold red text, so whatever value is put in B1 would display in bold red.

please advise? thanks

Take a look at the attached workbook. Columns F, G, H contain the percentages which correspond to the options selected by the user in Columns C, D, ECells F4, G4, H4 contain the maximum value of these percentages.Columns A and B are conditionally formatted so that the option and percentage cells corresponding to this maximum percentage are highlighted.Pressing the F9 key recalculates the various random numbers on the worksheet, and the appropriate option and percentage cells are again highlighted
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,

Just a question - I'm not sure exactly how to google what I want to
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

Hi,

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

I use conditional formating in my workbooks and was wondering if there is a way to use the same concept to change the color of a tab based on the value in a cell.

Thanks!

Hello.Can somebody tell me what the formula is to format one cell based on text in another cell eg If text in v 42 contains ! then format text color in u 42.Thanks in advance

Hi everyone,

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

I have an Excel 2007 s/sheet with two key input columns. One has the value "New" or "Stock" from a dropdown list. The other selects an item name from a dropdown list (or enters a new item name) based on VBA code found at http://www.contextures.com/excel-dat...ation-add.html. This code automatically adds the new item name to a data validation list.

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?

I have a large spreadsheet that I enter data into from one of our
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

Does anyone know how to conditionally format an entire row based on value of
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.

Hello,

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

Using vba, how do I set the values for column C (Range C2:C100) based on values in column A (Range A2:A100) AND column B (Range B2:B100). ??

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.


No luck finding an answer? You could always try Google.