Free Microsoft Excel 2013 Quick Reference

Conditional formatting columns based on criteria of 2 cells

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


Post your answer or comment

comments powered by Disqus
I need to count the number of nonblank cells in one column based on criteria
from cells in another column. Example: Column A contains years 2006, 2007,
2008. Column B contains rank such as Lt., Col., etc. I need to know the
total number of 2006 individuals who have the rank of Lt., the number of 2006
individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to have it
look at the other column for the type of count I need.

Hi everyone, I'm new to VBA and I'm trying to write a macro that will delete columns based on conditional formatting. Basically what I have is a range of cells, each cell has 2 conditional formatting criteria, both with the same conditions and if this condition is true, then the cell is highlighted yellow and if the condition is false, the text is white colored. What I want the macro to do is for each column in this range, check the CF of each cell, and if every cell in the column meets the second CF criterion (ie., the text is white colored), then delete the entire column. I've found some VBA code online for a function that checks for CF, and I'm trying to use that with some code I found for deleting blank columns. Here's what I have so far, with the line I modified in blue:


	VB:
	
 DeleteBlankColumns() 
    Dim Col As Long, ColCnt As Long, Rng As Range 
     
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
     
On Error Goto Exits: 
     
    If Selection.Columns.Count > 1 Then 
        Set Rng = Selection 
    Else 
        Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column())) 
    End If 
    ColCnt = 0 
    For Col = Rng.Columns.Count To 1 Step -1 
        [COLOR="Blue"]If Application.WorksheetFunction.CFColor(Rng.Columns(Col).EntireColumn) = 0 Then[/COLOR] 
        Rng.Columns(Col).EntireColumn.Delete 
        ColCnt = ColCnt + 1 
    End If 
Next Col 
 
Exits: 
Application.ScreenUpdating = True 
Application.Calculation = xlCalculationAutomatic 
 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This doesn't seem to do anything though, and since I really don't know much about VBA I thought I'd post here for some help. Thanks!

I asked this before but my explanation was labored and the answer didn't
address my needs. Here is a fuller, and I hope, clearer explanation. How do
add the figures in one or more columns based on criteria in more than one
column? For example assume the following worksheet:

A B C D E

1 aaa n n n
2 bbb zzz n n n
3 aaa yyy n n n
4 ccc xxx n n n
5 aaa www n n n
6 bbb n n n
7 bbb zzz n n n
8 ccc n n n

The real worksheet has hundreds of rows. I would like to add up the n's in
the rows that have a specific criteria, e.g. aaa in Column A and bull
(blanks) in Column B. I would also like to add up the n's in the rows that
have a specific criteria in Column A and anything in Column B (not null or
blank), e.g. aaa and yyy with the aaa and www. There are too many different
strings in Column B to use specific criteria. Any help would br greatly
appreciated. Setts

Hi,

I have an excel worksheet in which there are 1000 rows. Each row has 10
columns. Based on Criteria (ex. Cell C's value =TRUE and Cell F
value="Yummy") I want to fill the entire row with one color and the format
the cell (ex. E with the bold font and red color). The conditional formatting
doesn't work because of the two criteria. Can anybody please help? I would
love to get a non-macro solution but if there isn't any non-macro, I would be
willing to try the macro stuff.
Thanks in advance

I'm having issues coming up with a conditional format rule that highlights the top cell of a column based on if the bottom cell of that column has the greatest value in it's row.

This needs to be applied to 13 columns, so I want to highlight the top cell of the column that has the greatest value (based on the bottom row) of the 13 columns. I know I can do this via:

=AND($J$45>$L$45,$J$45>$M$45, $J$45>$N$45......etc etc applied to the top of the column. However, I would prefer not to do this for 13 columns 13 times. In the interest of expanding my knowledge, I wanted to find a simpler way. I thought of using the MAX function, but the bottom row of the column has formulas that result in a #DIV/0 error. Any way to do this?

Thanks!

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!

Hello,

I have an Excel sheet containing a list of items and their attributes. Each item can have up to eight unique attributes from a common pool of over 20 attributes. The item's name is in column A, attribute #1 in column B, attribute #2 in column C, etc. (Please see the attachment for a sample table in this format)

I would like to use conditional formatting to highlight all item names (in column A) that have a specific attribute (let's say "R", for example). I know how to do this using a series of nested IF statements, but that is messy and difficult to modify when needed. Is there any simpler way of doing so?

-Sklar

Hi,

I have an excel worksheet in which there are 1000 rows. Each row has 10
columns. Based on Criteria (ex. Cell C's value =TRUE and Cell F
value="Yummy") I want to fill the entire row with one color and the format
the cell (ex. E with the bold font and red color). The conditional formatting
doesn't work because of the two criteria. Can anybody please help? I would
love to get a non-macro solution but if there isn't any non-macro, I would be
willing to try the macro stuff.
Thanks in advance

Hi,

I have searched and can't seem to find a solution that works for me. Basically I have to sum up a column based on criteria in 2 other columns to provide summary information.

For example: Sum Column B where column A = Hardware AND column C = BB

I have attached a workbook with an example. Expected results are on the "summary" sheet.

Thanks in advance for any help. I know this is probably fairly simple but I am drawing a blank for some reason.

Brian

Hi

I want to do a count of unique occurrences of data in one column based on criteria in another column.

I have attached a sample worksheet with the data. I want to get the result in cell I26.

Thanks.

ltsolis

I'm trying to find a way to sum a column based on criteria in an adjacent column. In this case, I have a column that lists lengths of pipe with the location of the pipe listed in the adjacent column. I want to be able to sum up lengths based on the location criteria. My knowledge of Excel functions is fairly basic and I would appreciate any help.

Hello,

I need to add a comment based on color of the cell. I am using excel 2007 and conditional formatting that helps me color each selected cell I need. Now, after conditional formatting I have colored cells with f.e. 4 colors and I need to generate comment for each color automatically.

Can anyone help, Please?

Thanks so much!

Move cell value to left column based on Criteria

Hi,

I am trying to do the following and have tried Hlookup with If statement but can't get this to work. I Input 1 value (say 3000) in any cell between Column k37:V37, I would like this number to enter in same row to corresponding columns AA37:AL37 based on following Criteria in Column X37 if X37 =7 (then enter K37 to AA37) like for like - apr -apr if X37 =30 (then enter K37 to AB37) Apr(k37) to May(AB37) if X37 =60 (then enter K37 to AC37) Apr(k37 to Jun(AC37) 

Any help would be much appreciated. I'm hoping to do this with a macro if possible.

Many thanks in advance

Haz

Hi Team

I require code to conditional lock cells base on input of other cells with a reset button.

Please see attach file

Thank you for your help

Andy

Hi all,

At the moment I have 1 spreadsheet with 12 worksheets that I have to enter data into. I would rather enter all the data onto one sheet then have a macro move the data into the correct worksheet based on the contents of columns A and B.

Please find an example attached with dummy data.

The raw data will be entered into the worksheet "Data". Once complete, I would like the user to press a button and the macro to then identify from column A the suppliers name. If it is not one of the recognised suppliers, then it would move the row into the worksheet "one off". If it does recognise the supplier then it checks column B to see which of the two supplier's two worksheets it needs to copy it to, with the data entered into the correct column based on the column title (ie only the white columns).

Am I asking too much?

Thanks for your help

Hi guys,

I was wondering if anyone could think if it's possible to set the width of a column based of the contents of a specific range of cells, B32:B44.

I have tables of data on a worksheet that have headings in column B populated by VBA. What I would like to do is set the width of this column based on the contents of these cells alone as I have entries in other cells in the column that would mean using a normal column autofit command would not give the desired results.

Any thoughts appreciated as always.

Dom

I want to automatically fill in column B (from a seperate list) based on part
of the cell in column A.

For instance,
A312 = "MW3-11315AH"

Now, I would like to fill in B312 with "SHANK", based on the "MW3" at the
beginning of the text string in A312.

Also,
A313 = "B2-P-N49B-A0014"

I would like to fill in B313 with "ARM ASSY", based on the "A0014" at the
end of the text string in A313. However, the numerical portion of "A0014"
(A####) will change.

I was hoping to create a list on another worksheet, and use VLOOKUP, but I
am unsure as to the implementation of wild-card or partial-content lookups.

Any help is GREATLY appreciated!

I want to automatically fill in column B (from a seperate list) based on part
of the cell in column A.

For instance,
A312 = "MW3-11315AH"

Now, I would like to fill in B312 with "SHANK", based on the "MW3" at the
beginning of the text string in A312.

Also,
A313 = "B2-P-N49B-A0014"

I would like to fill in B313 with "ARM ASSY", based on the "A0014" at the
end of the text string in A313. However, the numerical portion of "A0014"
(A####) will change.

I was hoping to create a list on another worksheet, and use VLOOKUP, but I
am unsure as to the implementation of wild-card or partial-content lookups.

Any help is GREATLY appreciated!

Hi, I am a newbie at writing macros. I am trying to write a simple macro where I can hide columns based on a value selected from a drop-down list that I have in cell A3.

For instance if the user selects "NA" then I want columns D:I hidden
If the user selects "EUROPE" then I want columns B:C and columns F:I hidden
If the user selects "ASIA PACIFIC" then I want columns B:E hidden.

How can I accomplish this ? Would appreciate any help.

Ash

Greetings,
Forum was a big help last time and I have a more difficult challenge this time. I need to calcluate the time difference between two relative cells based on the criteria of another cell.
In the attached example, there are a group of records for each user (column D). I need to calculate the difference from B time (column D) the first instance of a new user's recoreds and the D time (column G)in the last user's record. In example, time difference between D2 and G60, then D61 and G77, etc. The cells will be in different locations each day. Basically, for each user the difference between their first b time and last d time....make sense?
Thanks in advance!!!

I have a "Workbook_SheetChange" VBA subcode that was supplied courtesy of one of the many very skilled individuals (Rbrhodes) on this board. It uses "IF/IfElse/Then" that I am attempting to alter to meet my needs. This code works wonderfully to hide rows based on criteria, but I have discovered that the I now need to use more criteria than I originally thought I requred, and while what I have included is brief, before I get even halway through adding all the variables I require, the code seems to simply cease to function?

I have found that I may need to use as many as 15 different criteria to differentiate and sort through to hide some of the rows. Unfortunately, the criteria is scattered throughout a number of worksheets, and NOT in any particular set of rows or columns on any of them (and I know that makes it even more difficult) and the examples I included below only use 6 different criteria.

I also have found that I now need it to "fire" the sub for ANY change made on the sheet, not just when a change is made to cell "C6" as was originally intended. The sample code shows rows 11 through 50, but there really is over 800 rows so this code gets really long and really involved using all the "RANGE/VALUE/AND's, and requires many many separate entries that each have multiple criteria. The criteria is all coming from choices made in various data validation list boxes.

Thanks and I hope I have not thoroughly confuzzled you all with my descriptions


	VB:
	
 
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.  Range) 
     
    Dim ws2 As  Worksheet 
     
     '//create object
    Set ws2 = Sheets(2) 
     
     'handle  errors
    On Error Goto endo 
     
     '//ADDED
    Application.EnableEvents = False 
     
     '//work with object
    With ws2 
         '//only if change happened on sheet "INPUT", cell C6
        If Not   Intersect(Sheets("INPUT").Range("C6"), Target) Is Nothing Then 
             
             '//Series of IF's - could be Select   Case or other logic
            If Range("INPUT!C6").Value = "0" Then '(This entry is used to reveal all rows)
                .Rows("11:50").Hidden = False 
                 
            ElseIf Range("INPUT!C6").Value = "100" And _ '(I changed to "And" from "Then")
                Range("INPUT!C11").Value = "Hydraulic" And _ '(I added this line)
                Range("INPUT!G6").Value = "Roller" And _ '(I added this line)
                Range("INPUT!G11").Value = "Left Hand" And _ '(I added this line)
                Range("INPUT!M6").Value = "Dump Cart" And _ '(I added this line)
                Range("INPUT!M11").Value = "Three Valve Manifold" Then '(Added this)
                    .Rows("11:14").Hidden = False 
                    .Rows("15:21").Hidden = True 
                    .Rows("22:28").Hidden = False 
                    .Rows("29").Hidden = True 
                    .Rows("30").Hidden = False 
                    .Rows("31:50").Hidden = True 
                     
                ElseIf Range("INPUT!C6").Value = "225" And _ 
                    Range("INPUT!C11").Value = "Hydraulic" And _ 
                    Range("INPUT!G6").Value = "Roller" And _ '(I added this line)
                    Range("INPUT!G11").Value = "Left Hand" And _ '(I added this line)
                    Range("INPUT!M6").Value = "Dump Cart" And _ 
                    Range("INPUT!M11").Value = "Three Valve Manifold" Then 
                        .Rows("11:30").Hidden = True 
                        .Rows("31:34").Hidden = False 
                        .Rows("35:41").Hidden = True 
                        .Rows("42:48").Hidden = False 
                        .Rows("49").Hidden = True 
                        .Rows("50").Hidden = False 
                         
                    Else 
                        .Rows("11:50").Hidden = True 
                    End If 
                End If 
            End With 
             
endo: 
             
             '//reset
            On Error Goto 0 
             '//cleanup
            Set ws2 = Nothing 
             
             '//ADDED
            Application.EnableEvents = True 
        End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,
I have a worksheet in a workbook where user can enter accumulated labor hours worked under a particular activity code on a daily basis. There are columns for 31 days. I like the user to see and use only 30 days columns for months which have 30 days and so on. For February users should see only 28 days or 29 days depending on if it is leap year or not. The format for the data is as follows:

Col-A Col-B Col-C Col-D ........... Col-AF Col-AG Col-AH Col-AJ
ActCD Description Day1 Day2 ..............Day30 Day31 Adjust AdjustedMTD
102 Truck driver 8.0 16.0 ..............100 2.0 102.0
103 Union Bus 8.0 16.0 ..............160 8.0 168.0

In other words Column C thru Column AG is used to enter accumulated hours.
Column AH is used to enter adjustment of hours for that activity. The Column AJ is the sum of hours for the last day of the month and the adjustment.
I like the user to be able to able to enter only hours and adjustments without worrying about changing the sum formula in the Col-AJ.
Through a table in another sheet I can pick up the beginning and ending dates of the month when user initializes the sheet for a new month.
So I am looking for a way to hide and unhide last 2 or three days columns based on the month of a particular year and dynamically change the sum formula in column AJ. For example for July 05 the sum formula is "=SUM(AG3+AH3) while in Sept 05 it should change to AF3+AH3 in row 3.

Can someone help me to with this formula?

I have 3 columns. Two of them have dollar amounts and the third one a date.
How to I add the 2 columns based on the date in the third column without
counting blank fields.

A 4,228.50
B 295.00
C January 12, 2005

So I'm trying to add up two columns based on criteria from each to give me a total. From below I would like to add up all that have say CMDY and all that have a Verified time larger then 16:00. The answer should be 12, 2 from CMDY and 10 greater then the verified time of 16:00.

I have tried =sumproduct and =countif but neither seems to give me the results I'm looking to get. Maybe I'm missing something in my formulas but I don't get a number I get an error. I attached a .xls what what it looks like in excel. I cleaned the attachment up a little so the formula below has a little different range.

This is what I have been trying to get working but
=SUMPRODUCT(G20:G144=CMDY)*(N20:N144>=Verified: 09/15/10 16:00))

006 54989A HD Forte 9.30 SSEA AENN 16:00-24:00 09/15/10 19:15:30 09/19/10 Verified: 09/15/10 19:16

016 54989A HD Forte 9.30 SSEA CMDY 06:00-24:00 09/15/10 16:18:30 09/19/10 Verified: 09/15/10 16:22

020 54989A HD Forte 9.30 SSEA CMDY 16:00-24:00 09/15/10 21:40:30 09/19/10 Verified: 09/15/10 21:33

024 54989A HD Forte 9.30 SSEA CNNN 06:00-24:00 09/15/10 22:31:30 09/19/10 Verified: 09/15/10 22:44

028 54989A HD Forte 9.30 SSEA CNNN 16:00-24:00 09/15/10 20:50:30 09/19/10 Verified: 09/15/10 20:56

066 54989A HD Forte 9.30 SSEA FSNW 16:00-24:00 09/15/10 22:45:00 09/19/10 Verified: 09/15/10 22:31

070 54989A HD Forte 9.30 SSEA FXXX 06:00-24:00 09/15/10 15:55:00 09/19/10 Verified: 09/15/10 15:57

082 54989A HD Forte 9.30 SSEA FXNC 16:00-24:00 09/15/10 19:20:30 09/19/10 Verified: 09/15/10 19:19

086 54989A HD Forte 9.30 SSEA HIST 06:00-24:00 09/15/10 08:45:30 09/19/10 Verified: 09/15/10 08:42

094 54989A HD Forte 9.30 SSEA LIFE 06:00-24:00 09/15/10 06:20:00 09/19/10 Verified: 09/15/10 06:28

107 54989A HD Forte 9.30 SSEA SPKK 06:00-24:00 09/15/10 07:29:30 09/19/10 Verified: 09/15/10 07:28

123 54989A HD Forte 9.30 SSEA TBSC 06:00-24:00 09/15/10 13:45:00 09/19/10 Verified: 09/15/10 13:42

127 54989A HD Forte 9.30 SSEA TBSC 16:00-24:00 09/15/10 18:27:30 09/19/10 Verified: 09/15/10 18:16

127 54989A HD Forte 9.30 SSEA TBSC 16:00-24:00 09/15/10 19:45:30 09/19/10 Verified: 09/15/10 19:46

139 54989A HD Forte 9.30 SSEA TNTT 06:00-24:00 09/15/10 08:45:30 09/19/10 Verified: 09/15/10 08:40

143 54989A HD Forte 9.30 SSEA TNTT 16:00-24:00 09/15/10 16:31:30 09/19/10 Verified: 09/15/10 16:15


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