Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Conditional Formatting - Multiple Rows and Columns

Hi. Can some one please help me figure out how to do some conditional formatting on multiple rows and multiple columns. For example, I have over 2000 records with employee names and the employee status (full time, part time). The employee name could show up numerous times depending on positions and other factors. The first step would be to highlight or group by the employee name, then by their status. Attached is a spreadsheet of what the end result should look like. The ultimate goal is to hightlight those employees that went from a PRN status to a RFT status, however they could of had multiple status' in between. I'm not sure how to do this. Any suggestions? Any help would greatly be appreciated. Thanks.


Post your answer or comment

comments powered by Disqus
Hello,

An array has its rows and columns marked 1-10. It shows probabilities of an occurrence specified for a row number, dependent on which column it crosses. However, for rows and columns marked with the same number it always shows 0.5, as it means probability of the occurrence when a row marker crosses "its own" column. The question: I don't know how to format the table conditionally so that it showed all "self-crossing" cells, that is row 1 vs column 1, row 2 vs column 2, etc. I have tried with INDEX, but to no avail. Could you help please? I don't want to format the cells by value but by coordinates, as I may happen to have some non-self-crossed 0.5s in the future, and I wouldn't want them formatted, for they'd obscure the view.

or is it restricted such that the lookup array can only be one column ie: A:A when finding row number or one row ie: 1:1 when finding column number

I'm using the index and match functions in combination and I want the lookup array in the match functions to be the same as the lookup array in the index function.

IE: lets say I want to search vertically for a list of work activity codes and I want to search horizontally for various metrics such as work accomplished, actual hours spent and so on.

but I don't want to always be restricted to having the raw data I'm searching in the same format where activity codes are always column B and the status/metrics on those activity codes is always row 4.

I want to be able to search an entire array of multiple rows and columns (or an entire spreadsheet) for a specific reference and have it return the row number that reference is in.

then I want to be able to search an entire array of multiple rows and columns (or an entire spreadsheet) for a another different specific reference, like hours spent, and then return the column number that reference is in

then i want to have the value returned that occurs at the intersection of that row and column either using the index function or some other function

I tried something like:

=Index(A1:AR90, Match(AC312, A1:AR90, 0), Match("Hours Spent", A1:AR90, 0))

But it doesn't work.

However If i have

=Index(A1:AR90, Match("ITXRP", C:C, 0), Match("Hours Spent", 1:1, 0))

It works just fine.

However this restricts me to always having to make sure the raw data export I get is in the format where Activity Code is in column C and the metrics headers are in Row 2.

I want the Functions to work whether I have the activity codes listed in Column M and the headers in row8, or activity codes in column E and Headers in row 4 etc.- to still work regardless of what column my activity codes are in and regardless of what row the metrics headers are in

I thought maybe a nested match like Match("IXRTP", then for array using another match that would return the column, but that match would have to have a specific row selected and you could use another nested match to get the row but then you would have to select a specific column to search.

I couldn't get it to work and I don't think that would work either because it seems to me it would run into a circular logic issue

Is there a function that will search an entire array of multiple rows and columns (A1:AC90) or an entire sheet and return a specific number for the row number and do the same for the column number such that it could be used in the index function or some other function

Thanks for any input

Good day everyone!!
I would really appreciate some help with a formula that can sum up Costs over multiple rows and columns based on the year to date. I attached a sample of the data I'm trying to work on.

I'm trying to do an analysis on a quarterly basis, e.g. Jan vs Mar.
For Jan vs Mar, I chose Mar in the drop down box and the Sales - Mar YTD will be shown in the Total column. For the Jan part, I used the formula "=SUM(D14:CHOOSE(E16,D14,E14,F14,G14,H14,I14,J14,K14,L14,M14,N14,O14))" with E16 showing "1" for the month of Jan.
As Sales is only on 1 row (Row 14), my colleague helped me with the above formula.

For Costs (which could be 4 rows or more), the Costs - Mar YTD is also shown in the Total column. However, for the Jan part (highlighted in red), I'm stumped. I could repeat a variation of "=SUM(D14:CHOOSE(E16,D14,E14,F14,G14,H14,I14,J14,K14,L14,M14,N14,O14))" 4 times, but is there any simpler way to do this? I'd like the option of switching up the months for analysis so a simple formula would really help.

Apologies if my question is "wordy" but I could really use some help as I'm not very Excel-savvy!!

Hello All,

I need help on solving one requirement where the data on multiple rows and columns has to be transposed to single horizontal row for each unique Id.
To be clear basically I Have the different cost data for same Id on multiple rows(Minimun 0 to max of 6 rows for a particular ID) with cost and description values in 2 columns and I want all the costs and description for a particular ID to be moved onto one single row as

ID1,Cost1,Desc1,Cost2,Desc2,Cost3,Desc3,Cost4,Desc4
ID2,Cost1,Desc1,Cost2,Desc2
ID3,Cost1,Desc1

etc.

I have attached the sheet with the actual data in Sheet1 and the desired data set in Sheet2.

Can anyone help me on this please.
Thanks in Advance.

Regards
SK

Hi ,

I am very new to Excel and VBA programming .
My requirement is as follows
For a range of data in my excel ,
a) I want to highlight entire row in "Gray" Color if that row contain value "US" , "UK" in any cell in that perticular row . I also want to lock this row
b) I want to highlight entore column in "Gray" that containing a value "REVISED" in any cell in that column.

Note that I want to search entire range and then apply above conditional formatting .

I tried to search it on Goolgle and this forum .
But Since I am new to VBA I am not able to judge if a perticular code is useful for me

Could you pl help me out .

Regards
Amit

Hi guys,

I want to create a formula that can easily generate a report of each unique name and their corresponding single and/or multiple results from different rows and columns of data. If you see the attached excel sheet, I created something close to what I wanted but it doesn't generate the other columns of data and also how can I make the formula generate a report for each unique name in column one?

Thanks a lot for your help!

hey guys, i'm trying to create a conditional format per row and not having much luck.

What I am trying to do is see if the cell is greater than or less than column C of the same row and colour it either green or red (green if lower, red if higher)

The purpose of this is to manage monthly outgoings, so if the value for the month (each month is on a different column) is over the budjet it should show red to indicate a problem.

I tried using a formula =VALUE(ADDRESS(ROW(), 3)) but that generates an error.

I have attached the excel file as an example PLEASE HELP!!!

I have a problem which I have not been able to solve.
I have 2 worksheets, 1 with 3000 rows of data consisting of 14 columns. The key Column describing a product Code. Call this main worksheet 1

I have another worksheet with product codes and descriptions that are unique to a certain supplier who supplies these products. call this worksheet 2

I need to retrieve in order to create a report the data (rows and columns) from worksheet 1 that match the product codes in worksheet 2. In other words I am trying to get only certain codes that match a certain supplier. The codes that are unique to the supplier that i want info on are in worksheet 2
I have tried the index match functions with no luck
I can provide a sample of the worksheets if needed.
Hoping you can offer a suggestion.
Dan H

I have a spreadsheet will multiple rows and columns. As an example:

Col A B C D E

ROW 1 2 3 4 5

COL A will have letters ranging from a - w down through the rows
COL B will have an "X" indicating the col applies to any one of the letters in the coresponding row.
COL C will have a number range from 1 - 60.

I would like to search the table and determine what rows share the criteria. If I would like to find where w X and 60 coincide in the same row to count that row as 1... if it were in another row then it would add 1 and total 2.. and so on.. sorry if I am a little vauge..

Thanks for your help!

I want to place conditional formatting on about 120 rows, over columns A-G. The condition is that if the value of Cell Gn = "This", then columns A-G of Row n should be bold and italicized. I can get it to work for one row at a time, but not for the entire 120 rows. Setting the condition 120 times is not productive. Is there a way to do what I want to do? I am using Excel 2007. Thank you.

Hello,

I have an Excel worksheet with about 300 rows and 8 columns per row. I want to shade the highest number and the lowest number for each row. I assume the built in conditional formatting is the best place to start, but I don't really know.

Sample worksheet attached. Thanks in advance for reading or responding to this post.

NW

Hi,
I'm currently working with big datasheet (>100k rows) and would like to make it even bigger.
The overall scheme looks like "Before" spreadsheet in the attached file.
What I need to do is divide values separated by space from "C" column into multiple rows and automatically fill-in corresponding "A" and "B" columns, so it should look like in "After" spreadsheet.

Could you please help me and tell me if and how is that possible?
Thank you for your support.

Hello,

I am trying to select data that is formatted such that there is an empty row and an ampty column between each data entry. My goal is to select these entries so that I can format them and then apply formulas to them later on.

I don't know much about VBA or macros and up until about 4 days ago I had never used them. However, I was able to find some code online which I tweaked a little to do what I it to do. This is the code that I have right now:

	VB:
	
 SelectEverySecondCellInColumn3() 
    Dim lRow As Integer, rEverySecond As Range, cColumn As Integer 
    On Error Resume Next 
    For cColumn = 2 To 50 Step 2 
        For lRow = 2 To 60 Step 2 
            If rEverySecond Is Nothing Then 
                Set rEverySecond = Cells(lRow, cColumn) 
            Else 
                Set rEverySecond = Union(rEverySecond, Cells(lRow, cColumn)) 
            End If 
        Next lRow 
    Next cColumn 
    Application.Goto rEverySecond 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now the problem is that as soon as I use an increment of 2 for my loops, the program stops at a random place without reaching 60 rows and 50 columns. If I keep running the program again, sometimes it will stop at the same place and at other times it will stop at a different location. If I use an increment of 1 (ie. Step 1), it works fine everytime. I can't figure out what is wrong. I'll greatly appreciate any help. Thanks

I have code to consolidate multiple workbooks into a new workbook (each workbook with only one worksheet). This works however I am not able to do the following and need help to include in this code if possible.

1. Delete all blank rows and columns
2. Delete repeated (duplicates) title
3. sort by name

Below is the code that I have.

	VB:
	
 SetCurrentDirectoryA Lib _ 
"kernel32" (ByVal lpPathName As String) As Long 
Sub ChDirNet(szPath As String) 
    SetCurrentDirectoryA szPath 
End Sub 

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

	VB:
	
 Combine_Workbooks_Select_Files() 
    Dim MyPath As String 
    Dim SourceRcount As Long, Fnum As Long 
    Dim mybook As Workbook, BaseWks As Worksheet 
    Dim sourceRange As Range, destrange As Range 
    Dim rnum As Long, CalcMode As Long 
    Dim SaveDriveDir As String 
    Dim FName As Variant 
    With Application 
        CalcMode = .Calculation 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        .EnableEvents = False 
    End With 
    SaveDriveDir = CurDir 
    ChDirNet "C:" 
    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _ 
    MultiSelect:=True) 
    If IsArray(FName) Then 
        Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) 
        rnum = 1 
        For Fnum = LBound(FName) To UBound(FName) 
            Set mybook = Nothing 
            On Error Resume Next 
            Set mybook = Workbooks.Open(FName(Fnum)) 
            On Error Goto 0 
            If Not mybook Is Nothing Then 
                On Error Resume Next 
                With mybook.Worksheets(1) 
                    Set sourceRange = .Range("A33:R48") 
                End With 
                If Err.Number > 0 Then 
                    Err.Clear 
                    Set sourceRange = Nothing 
                Else 
                    If sourceRange.Columns.Count >= BaseWks.Columns.Count Then 
                        Set sourceRange = Nothing 
                    End If 
                End If 
                On Error Goto 0 
                If Not sourceRange Is Nothing Then 
                    SourceRcount = sourceRange.Rows.Count 
                    If rnum + SourceRcount >= BaseWks.Rows.Count Then 
                        MsgBox "Not enough rows in the sheet. " 
                        BaseWks.Columns.AutoFit 
                        mybook.Close savechanges:=False 
                        Goto ExitTheSub 
                    Else 
                        Set destrange = BaseWks.Range("A" & rnum) 
                        With sourceRange 
                            Set destrange = destrange. _ 
                            Resize(.Rows.Count, .Columns.Count) 
                        End With 
                        destrange.Value = sourceRange.Value 
                        rnum = rnum + SourceRcount 
                    End If 
                End If 
                mybook.Close savechanges:=False 
            End If 
        Next Fnum 
        BaseWks.Columns.AutoFit 
    End If 
ExitTheSub: 
    With Application 
        .ScreenUpdating = True 
        .EnableEvents = True 
        .Calculation = CalcMode 
    End With 
    ChDirNet SaveDriveDir 
End Sub 

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


How do I set the Conditional Formatting in a column where conditions in a row and column matches each other at a same time, so that those duplicate items will be highlighted?

For example in the attachment, I want those BKB branches (C8 & C13) where Model A (D8 & D13) have the same value will be highlighted as RED in column C. At the same time, other branches which are same (KLH for instant-C10 & C15), where the model column (E10 & E15) have the same value are also highlighted.

Those branch name that are same (IPR - C12 & C16), where their values are same but in different column (E12 & F16) will not be affected.

How do I do so? Is this possible?

Thank you.

Hi All

I have a problem where I need to format a list of data into rows and columns. For example, say the information is in cells A1 to A100, I would want:
cell A2 moving to B1
cell A3 moving to C1
..........
Cell A10 moving to J1
then
Cell A11 moving to A2
Call A12 moving to B2
.........
Cell A20 moving to J2

and so on until I reach the bottom I the list of data (which could be many thousands of rows long).

I was wondering if anyone could suggest a simple macro that would automate this process.

Yours hopefully

Phil

Hi

I was wondering if someone could help me out with a formula for conditional formatting.

I have several columns and rows on my worksheet. When I type information into Column K Row 3 I would like all of Row 3 to change to Red. If I type information in Column K Row 4 I would like the Row to change to Red.

I hope the above makes sense.

Thanks in advance for reading this post and Thanks in advance if you are able to help me out.

there are two columns.
the first column is the key column, and the second one is the value column.
I want to conditional-format the rows which is the same key with
different values.
Help me.

Can I set a filter for a merged column across multiple rows and on selection
of a value on the merged column get all the corresponding rows. Currently it
just returns only the first row.

I found the code below on http://www.xcelfiles.com/Excel02.html. It is so close to what I need to do. Dear Juan Pablo, if you read this post, I would love your help in making a little adjustment to the code you have below.

It highlights active row and column, but it overwrites the already colored cells. I need it to highlight active row and column while it keeps the already highlighted cells in their original color. So, say I have some cells in yellow. When the code highlights the active row and column in (say) purple, I would like to be able to see the the whole row and column in purple while the yellow cells remain in yellow.

So can anyone tell me how to make the code below keep the original cell color of the already highlighted cells while it highlights the rest of the cells in the active row and column? Pleasee help?

Thank you!
Option Explicit
'/////////////////////////////////////////////////////
'// Amended 14th Feb 2003 - suggestion by Juan Pablo G.
'// International versons may NOT recognise TRUE
'// Suggestion use =1 which evaluates to TRUE,
'// in fact any number that <> 0
'////////////////////////////////////////////////////

Const iInternational As Integer = Not (0)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!

'// On error resume in case
'// user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex
'// Leave On Error ON for Row offset errors

If iColor < 0 Then
    iColor = 36
Else
    iColor = 36 

End If

'// Need this test incase Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1

Cells.FormatConditions.Delete

'// Horizontal color banding
With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
    .FormatConditions.Add Type:=2, Formula1:=iInternational 'Or just 1 '"TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

'// Vertical color banding
With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address)
    .FormatConditions.Add Type:=2, Formula1:=iInternational 'Or just 1 '"TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

End Sub


Is there anyway to highlight the row and column of the active cell without permanently removing either the conditional formatting in the highlighted row and column or the colour formats? I have the following code:

Private Sub
Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
Rows(Target.Row).Interior.ColorIndex = 6
Columns(Target.Column).Interior.ColorIndex = 6
End Sub
But this gets rid of all conditional formatting in the active sheet, not ideal. Also, when I delete the code to stop using it the last row and column that was highlighted yellow remains yellow.

I know this might be a bit to ask but can anyone produce VBA code that will full fill my wish of being able to highlight the row and column of the active cell without ruining colour formatting?

Thanks in advance

I have a report that I need to reformat where part of the information is moved from rows to columns.

The report is broken up into "sections" as follows: Each “section” is a series of multiple rows and is broken down as follows:

ROW 1: Contains data (in a single cell) about a Sales rep, which includes (1) rep number & (2) rep name
NEXT ROW(s): Contains data information about an invoice(s), which includes date, invoice number, client name, trans ID, etc. The invoice data can be one row up to as many as 500 rows
LAST ROW: Contains the Rep Subtotal

I need to spit out a report that contains the invoice data only (the middle part of the “section”). I don't want "ROW 1" or "LAST ROW" of each section in the output. For each invoice row, I need to include the rep number and the rep name for each invoice. As noted, the rep number and name is always listed in the row preceding the invoice data. The format is always a 6-digit code followed by the name. So I need to split the data into two pieces.

Hope this makes since. I have attached a sample for clarification, which includes the raw data and a sample of the desired output. Thank you in advance for any comments.

I have a sheet that has conditional formatting with three conditions, that change the background color for two rows depending on the value of certain cells within those two rows.

My the way my spreadsheet is made, each "entry" is really two rows of information, and then a shaded row to divide each "entry".

I have about 90 of these "three row entries". So the question is, now that i have defined my conditional formatting for the first three lines, how do i copy that formatting so that the rest of the spreadsheet will work the same?

When i try to copy it now, it wants to do it line by line, rather than copying it in groups of three lines. Hopefully this makes sense.. Any help would be appreciated.

I've created conditional formatting that compares the value of one cell to another (J3 and K3 for example) and changes the cell color. If K3 is greater than J3, highlight K3 green; if less than/equal to, highlight red.

However, I want this rule to apply for 150+ rows, so that J4 and K4 are compared, J5 and K5 compared, etc. Copying and pasting seems to compare all values to J3 instead of the successive rows. How do I fix this?

I hope this is clear, and it's my first post so my apologies if I've made any egregious errors. Thanks!


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