Excel Macro to Change Color of a Row Based on Column and Cell Value

how to change a row color based on a text value in a cell
excel change cell color based on value of another cell
excel alternate row color based on cell value
excel formula to change cell color based on text
excel change row color based on dropdown selection
excel conditional formatting row based on cell text
excel select rows based on cell value
how to change row color based on a single cell's value

I have the below code and would like it to be modified to work on all worksheets in the active workbook. Also, it currently does the entire row, but is there a way to modify it to only highlight the row until the data stops (example: A-J or A-C etc. not A-~)?

Sub ChangeColor()

lRow = Range("A" & Rows.Count).End(xlUp).Row
Set MR = Range("A2:K2" & lRow)
For Each cell In MR
    If cell.Value = "CENTRL DISTRICT" Then cell.EntireRow.Interior.ColorIndex = 10
    If cell.Value = "KC DISTRICT" Then cell.EntireRow.Interior.ColorIndex = 3
    If cell.Value = "NE DISTRICT" Then cell.EntireRow.Interior.ColorIndex = 11
    If cell.Value = "SE DISTRICT" Then cell.EntireRow.Interior.ColorIndex = 30
    If cell.Value = "ST LOUIS DIST" Then cell.EntireRow.Interior.ColorIndex = 12
    If cell.Value = "SW DISTRICT" Then cell.EntireRow.Interior.ColorIndex = 13
Next

End Sub

You can use the below, you needed to change the range in the if statement.

You haven't given worksheet names here, but you should really fully reference all your ranges too.

Sub ChangeColor()
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    Set MR = Range("A2:K2" & lRow)
    For Each cell In MR
        If cell.Value = "CENTRL DISTRICT" Then Range("A" & cell.Row & ":J" & cell.Row).Interior.ColorIndex = 10
        If cell.Value = "KC DISTRICT" Then Range("A" & cell.Row & ":J" & cell.Row).Interior.ColorIndex = 3
        If cell.Value = "NE DISTRICT" Then Range("A" & cell.Row & ":J" & cell.Row).Interior.ColorIndex = 11
        If cell.Value = "SE DISTRICT" Then Range("A" & cell.Row & ":J" & cell.Row).Interior.ColorIndex = 30
        If cell.Value = "ST LOUIS DIST" Then Range("A" & cell.Row & ":J" & cell.Row).Interior.ColorIndex = 12
        If cell.Value = "SW DISTRICT" Then Range("A" & cell.Row & ":J" & cell.Row).Interior.ColorIndex = 13
    Next
End Sub

Excel: Change the row color based on cell value, In the dialog box, select "Use a formula to determine which cells to format" In the text box below "Format values where this formula is true:", enter the following formula: =($I2="Due") (The $ before the I is important!) Click the "Format" button. Set the fill color to yellow, then click OK. Excel Macro: How to change the cell color of a row within a range. 0. VBA code to copy value from one cell to specific column. 0. row background color based on

This is simular to some of the above ... difference is it will hi-lite from the beginning to the last used column. It also cycles through all the sheets in the activeworkbook. It assumes the District Name is in column A.

Sub ChangeColor()

    For x = 1 To ActiveWorkbook.Sheets.Count

    Sheets(x).Select

    lRow = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
    lCol = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column
    Set MR = Range("A2:A" & lRow)

      For Each cell In MR
        If cell.Value = "CENTRL DISTRICT" Then Range(Cells(cell.Row, 1), Cells(cell.Row, lCol)).Interior.ColorIndex = 10
        If cell.Value = "KC DISTRICT" Then Range(Cells(cell.Row, 1), Cells(cell.Row, lCol)).Interior.ColorIndex = 3
        If cell.Value = "NE DISTRICT" Then Range(Cells(cell.Row, 1), Cells(cell.Row, lCol)).Interior.ColorIndex = 11
        If cell.Value = "SE DISTRICT" Then Range(Cells(cell.Row, 1), Cells(cell.Row, lCol)).Interior.ColorIndex = 30
        If cell.Value = "ST LOUIS DIST" Then Range(Cells(cell.Row, 1), Cells(cell.Row, lCol)).Interior.ColorIndex = 12
        If cell.Value = "SW DISTRICT" Then Range(Cells(cell.Row, 1), Cells(cell.Row, lCol)).Interior.ColorIndex = 13
      Next

    Next x

End Sub

macro to highlight row based on cell value, Watch Video – Highlight Rows based on Cell Values in Excel the cell highlighted, you may want to highlight the entire row (or column) based on In the dialog box that opens, set the color in which you want the row to get highlighted. How can we adjust this VBA code to only format the data from the active row that is� But sometimes, instead of just getting the cell highlighted, you may want to highlight the entire row (or column) based on the value in one cell. To give you an example, below I have a dataset where I have highlighted all the rows where the name of the Sales Rep is Bob.

The code below will loop through all sheets in ActiveWorkbook (even though I feel strongly against using ActiveWorkbook), and per each sheet loop through MR range.

I've replaced your multiple Ifs with Select Case.

Code

Option Explicit

Sub ChangeColor()

Dim lRow As Long, lCol As Long
Dim MR As Range, Cell As Range
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
    With ws
        lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set MR = .Range("A2:K2" & lRow)
        For Each Cell In MR
            lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column ' <-- in case the last column is different per row
            Select Case Cell.Value
                Case "CENTRL DISTRICT"
                    Cell.Resize(1, lCol - Cell.Column + 1).Interior.ColorIndex = 10
                Case "KC DISTRICT"
                    Cell.Resize(1, lCol - Cell.Column + 1).Interior.ColorIndex = 3
                Case "NE DISTRICT"
                    Cell.Resize(1, lCol - Cell.Column + 1).Interior.ColorIndex = 11
                Case "SE DISTRICT"
                    Cell.Resize(1, lCol - Cell.Column + 1).Interior.ColorIndex = 30
                Case "ST LOUIS DIST"
                    Cell.Resize(1, lCol - Cell.Column + 1).Interior.ColorIndex = 12
                Case "SW DISTRICT"
                    Cell.Resize(1, lCol - Cell.Column + 1).Interior.ColorIndex = 13
            End Select
        Next Cell
    End With
Next ws

End Sub

Highlight Rows Based on a Cell Value in Excel (Conditional , Excel VBA Macro: Change Row Color (Based on Cell Value) - We a specific value in a Duration: 4:52 Posted: Apr 7, 2019 Then click OK > OK to close the dialogs, and the rows have been highlighted which cell value changes based on column A. Note: Conditional Formatting tool is a dynamic function, if you change values in column A or insert new row between the data, the formatting will be adjusted as well.

Try this (note I have added declarations to your code).

Sub ChangeColor()

Dim lRow As Long, MR As Range, cell As Range, ws As Worksheet, lCol As Long
Dim wf As WorksheetFunction

Set wf = WorksheetFunction

For Each ws In Worksheets
    lRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    lCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    Set MR = ws.Range("A2").Resize(lRow - 1, lCol)
    For Each cell In MR
        If cell.Value = "CENTRL DISTRICT" Then ws.Cells(cell.Row, 1).Resize(, lCol).Interior.ColorIndex = 10
        If cell.Value = "KC DISTRICT" Then ws.Cells(cell.Row, 1).Resize(, lCol).Interior.ColorIndex = 3
        If cell.Value = "NE DISTRICT" Then ws.Cells(cell.Row, 1).Resize(, lCol).Interior.ColorIndex = 11
        If cell.Value = "SE DISTRICT" Then ws.Cells(cell.Row, 1).Resize(, lCol).Interior.ColorIndex = 30
        If cell.Value = "ST LOUIS DIST" Then ws.Cells(cell.Row, 1).Resize(, lCol).Interior.ColorIndex = 12
        If cell.Value = "SW DISTRICT" Then ws.Cells(cell.Row, 1).Resize(, lCol).Interior.ColorIndex = 13
    Next cell
Next ws

End Sub

Excel VBA Macro: Change Row Color (Based on Cell Value , For example, you can highlight a particular cell based on a condition. 1) Change the column width and row height in Excel using VBA: Do you know you Row).Interior.Color = vbRed Rows(Target.Row).Font.Color = vbWhite End If End If� Change a cell's color based on its current value (statically) - Once set, the background color will not change no matter how the cell's value changes. Change color of special cells (blanks / with errors / with formulas) How to change a cell's color based on value in Excel dynamically. The background color will change dependent on the cell's value.

This should work for you. You can set a begin and end point to your liking just by changing the letters, which is a reference to the column letters. Range("A" & n, "G" & n) This is a simpler way to get what you want.

Hope I helped.

    Sub ChangeColor()
    Dim nlast As Long

    Sheets("sheetname").Activate
    Set sht = ActiveWorkbook.ActiveSheet
        nlast = Cells(Rows.Count, "A").End(xlUp).Row

                For n = nlast To 1 Step -1
                 If sht.Cells(n, "A").Value = "CENTRL DISTRICT" Then sht.Range("A" & n, "G" & n).Interior.ColorIndex = 10
                 If sht.Cells(n, "A").Value = "KC DISTRICT" Then sht.Range("A" & n, "G" & n).Interior.ColorIndex = 3
                 If sht.Cells(n, "A").Value = "NE DISTRICT" Then sht.Range("A" & n, "G" & n).Interior.ColorIndex = 11
                 If sht.Cells(n, "A").Value = "SE DISTRICT" Then sht.Range("A" & n, "G" & n).Interior.ColorIndex = 30
                 If sht.Cells(n, "A").Value = "ST DISTRICT" Then sht.Range("A" & n, "G" & n).Interior.ColorIndex = 12
                 If sht.Cells(n, "A").Value = "SW DISTRICT" Then sht.Range("A" & n, "G" & n).Interior.ColorIndex = 13
            Next n
     End Sub

Highlight an Entire Row in Excel Based on a Cell Value using VBA , Discover how to format an entire row of cells based on the value of She wanted to highlight the entire rows in a data set when the value in a cell in This is a great feature of Excel that brings life to our spreadsheets All rows that contain a 6 in column E are immediately formatted with the font & fill color I� Download the Excel File. If you'd like to download the same file that I use in the video so you can see how it works firsthand, here it is: Conditional Formatting Based On Cell Value.xlsx (138.7 KB) Format an Entire Row Based on a Cell Value. Sometimes our spreadsheets can be overwhelming to our readers.

How to Apply Conditional Formatting to Rows Based on Cell Value , This Excel tutorial explains how to write a macro to change the background color MS Excel 2003: Change the background color of a row based on a cell value I'd like to change the background of a row to blue, if the UPC Code in column C� Vba change cell color based on value - Best answers; Changing a cell value based on the color of a - Forum - Office Software; Conditional formatting: change fontcolor based on another cell - Forum - Excel; Change color to different cells based on another cells text.

MS Excel 2003: Change the background color of a row based on a , But i need to highlight only rows that are having cell value as "Break I need to color rows based on the other values on the column G with some other color. Very small change [vba] Sub Macro1() Const TEST_COLUMN As String = "D" [/ VBA]. MVP (Excel 2008-2010) Post a workbook with sample data� Change a specified cell value when checkbox is checked with VBA code. You can also change a specified cell value when checkbox is checked with VBA code. Please do as follows. 1. Right click the Sheet Tab with the cell value you need to change based on checkbox, then click View Code from the right-clicking menu. 2.

Solved: Highlight ROW based on cell value, Use Excel conditional formatting to set rules for cell formatting based on For example, if the values in column B are over a set value, make the row blue we'll colour cells blue, if the number of units, in column B, is greater� To run a macro code by changing a cell value, the following VBA code can do you a favor, please do as this: 1.Right click the sheet tab that you want to execute the macro if cell value changes, and then choose View Code from the context menu, and in the opened Microsoft Visual Basic for applications window, copy and paste the following code into the blank Module:

Comments
  • Just use conditional formatting
  • How do you handle a scenarios where let's say Cell A3 ="CENTRL DISTRICT" , and Cell C3 = "KC DISTRICT" , where does each coloring starts and ends ?
  • @ShaiRado - good question, that hadn't occurred to me.
  • They are grouped by the district so it will only ever be all central then all kc etc.
  • @DefcaTrick so only one in a row ?
  • Hello, It didnt go through every worksheet, but otherwise this worked well.
  • don't forget to fully qualify your Rows.Count and Columns.Count add ws as a prefix
  • Hello, sorry im new ish to macro, how would I qualify the rows and columns? also I ran yours and changed the following because the districts are in E not A and it only highlighted E-H missing A,I,J,K...did I miss something? Set MR = ws.Range("E2:E" & lRow) ty
  • Before I change the code, can I check if you are just checking column E for these values or can they be in other columns too?
  • They will be in column E and A however A is grouped, so it shows the name in A2 then blanks down to A52 where it gets the next district ...because they are grouped and can be toggled or rolled up by that column
  • Code amended above.
  • Hello, this works great, thank you. Can it by any chance be changed to have something similar to the nlast but for columns?
  • So if one of these strings are found in a row, highlight the columns?