Color Banding Of Arbitrary Range
I know that if I select say A1:C100 on a spreadsheet, I can color alternating rows using conditional formatting and the formula =MOD( COUNTA($A$1:$A1),2). I am using COUNTA -- rather than simply ROW() -- so that I am only considering visible rows. I am trying to write a VBA subroutine that takes in an arbitrary range and colors in every other row of that range. Something like:
Sub FormatRange(ByRef theRange As Range, ByVal theColor As Integer)
theRange.FormatConditions.Delete
theRange.FormatConditions.Add Type:=xlExpression, Formula1:= "=MOD(COUNTA($A$1:$A1),2)"
theRange.FormatConditions(1).Interior.ColorIndex = theColor
End Sub
However I want to make the argument to COUNTA refer to the first column in theRange, not necessarily column A in the spreadsheet.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Color Banding To Locate Active Cell
I used the method described here: [url] The problem is that it seems only to work on the computer on which I made the sheet. This should mean that it is somehow dependent on some local settings. Does anybody know what settings, or whether it is something else that may be the cause?
View Replies!
View Related
Row Banding
i am having problems with the conditional formating function. to make document i am working on for office a lot more clear i was hopeing to do some row banding. i think you can get some basic row banding in auto format but i was hoping to do it myself. when i go to conditional formating - i change the tab to Formula is: ...
View Replies!
View Related
Conditional Formatting For Row Banding Doesn't Work After Autofilter
For obvious reasons, the conditional formatting to shade alternate rows doesn't work when filtered. So I think I need another way of doing it. Luckily, my table is fairly static, rows aren't added or removed. The first column is excluded from the banding. As is the first and last row with data (1 & 67) respectively. I found a relevant thread here, but the code is beyond my understanding.
View Replies!
View Related
Setting Color Of Range Based On Adjacent Cell Color
This is probably elementary, but I'm struggling and would appreciate any help as I have very little excel VBA experience to draw from. I have assembled code which changes the cell color based on a value change in Column A. Column A will contain many different groups of repeating values. This code works well and and I have been able to figure out how to limit the number of colors to only 2. The end result is each set of similar values in column A is visually grouped by one of two alternating colors. The number rows in the data set is variable as the data set is extracted from SAP. The number of columns is fixed. What I want to do now is set the cell color in columns B through F the same color that was assigned to the row in column A. So if cell A3 is set to colorindex = 6, then I want to set the range of cells B3 to E3 to the same color. Here is the code I am using to set the color of the cells in Column A:
View Replies!
View Related
Return Maximum Of Active Cell & Arbitrary Value To Cell
I am looking for VBA that will add the value of the current active cell on the sheet to the value in cell F12. The maximum value of F12 cannot exceed 1000. So if the value in F12 = 950 and 100 is the value in the active cell the maximum value in F12 should show 1000, not 1050. It should do this on the click of a button.
View Replies!
View Related
Color Range Between Specified Values
I'm using this conditional format formula (compliments of "shg") = COUNTIF($T$16:$T16, "R") * COUNTIF($T16:$T$600, "R") Which fills a color (Red) based on a specified value, up to the last entry. The conditional format is within a range ("Range1") in column P. The formula looks in column T for a value of "R" , in which it fills the cells between the first an last value with the color Red. as a brief example: Column..P,.....T 1..................X 2........Red.....R 3........Red.....X 4........Red.....R 5........Red.....X 6........Red.....X 7........Red.....R 8..................X 9..................X However my workbook has become very slow.. I have several columns utilizing this conditional format .(albeit trigered by a different letter value) Is there a way to incorporate this functionality into a macro, one which i can initiate via button assignment?
View Replies!
View Related
Fill Color In Range Between Specified Value
Trying to find a way to fill in a color based on a specified value, up to the last entry. ie. Within a range ("Range1")T1:T9, anywhere the value is "R" , fill the color red within column P (offset -5 columns to the left) up to the last entry. ..this is something I would like to initiate via button assignment using a VBA script. eg: Column..P,.....T 1..................B 2........Red.....R 3........Red.....T 4........Red.....R 5........Red.....V 6........Red.....M 7........Red.....R 8..................X 9..................X
View Replies!
View Related
Color Range With User Input
I have searched for code in the forum to help me here but could not get any of the possible choices to work for me. I have a spreadsheet that is password protected but would like the user to be able to change the color of the fonts based on the user's choice. The protected page (one of many in the spreadsheet) has various cells that are unlocked so that the user can enter data. The rest of the page is locked. I have allowed all users of the worksheet to Select Unlocked Cells only. I have attempted to allow them to Format cells as well, but each time the program is re opened this feature is no longer working. (The program has a macro that ensures it opens in protected mode each time.) So I would like to set up a Macro that allows the users to set the color of 6 rows and 5 columns based on their choice. An example of the configuration is: 1234Color Choice A11016221 B21117232 C31218243 D41319254 E51420265 F61521276 Using the above format, I am imagining the I would have a Key with color codes (using the ColorIndex Properties). The user would type in the appropriate number for each row and click a button and the rows (5 cells each) would change to the selected color.
View Replies!
View Related
Fill Color Change With Range
Function BG(InRange As Range) Range("InRange").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End Function That so far but not quite sure why that isnt working. I want to change the fill color with a UDF that all they do is select a Range and it changes those fill colors to whatever the Colorindex may be. I didnt find anything while searching the forums with this already.
View Replies!
View Related
Color Cells Within Numeric Range
Trying to make an excel macro that changes the background of a cell dependant if the value is between one number and another or equal to another number. Cell values = a1 = 250 a2 = 475 a3 = 715 vba Example: Case Is > 200 and < 400 colchoice = 4 Case Is >450 and < 550 colchoice = 5 Case is >600 and <700 or = 715 colchoice = 6 If i run the macro the cell background should be a1 =4 a2 =5 a3 =6
View Replies!
View Related
Max Date In Color Range
I have a spreadsheet (I've attached an example) that has a date stamp for when column B is updated. What I would like is to put into cell a1, the max date/time that the yellow cells have. This is the code that I currently have on Sheet1 to put the time stamp in. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then Application.EnableEvents = False Target.Offset.Offset(0, 2) = Now() Application.EnableEvents = True End If End Sub
View Replies!
View Related
Loop Through Range By Color Index
I have a bit of VBA code that loops through a range and looks at the color index. If it is color index three then it will put a "1" in the cell six columns over. This code works, but I see over and over again that loops are bad and inefficient. Since I am working on my code being more efficient I wanted suggestions for altrenate code that would do basically the same thing. Dim Bcell As Range For Each Bcell In Range("D2:D304") If Bcell.Interior.ColorIndex = 3 Then Bcell.Offset(0, 6) = "1" Else Bcell.Offset(0, 6).ClearContents End If Next Bcell
View Replies!
View Related
Color Offset Of Named Range
I've created a named range "Row_16" H16:M16. I've made a macro which colors the interior of this range.. Sub Fill_Row16() Range("Row_16").Select With Selection.Interior .ColorIndex = 48 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End Sub I would also like to add a secondary color (Red) to the row just below this range ie H17:M17. Is there a way to offset/ select an entire range.?
View Replies!
View Related
Color Cells Within Numeric Range/Scope
I'm trying to do some sort of a league table I have something like this with data in around 1000rows there is text in collums A B D and E. A B C D E 1 2 100 3 60 4 30 5 20 and i would like to do this: if value in cell x in collum C is over 80 then background color in Ax,Bx,Cx,Dx,Ex is yellow if value in cell x in collum C is between 60-80 then background color in Ax,Bx,Cx,Dx,Ex is brown if value in cell x in collum C is between 40-60 then background color in Ax,Bx,Cx,Dx,Ex is blue if value in cell x in collum C is between 0-40 then background color in Ax,Bx,Cx,Dx,Ex is red Obviously i cant use conditional formating because i have more then 3 conditions. Does anybody know what code to use for VBA?
View Replies!
View Related
VBA To Color Range Based On Cell Value
if you have an error on a spreadsheet, why doesnt this come under " case else" when vba is run past it? I have this simple code, but it doesnt work when the value on the spreadsheet is an error ("N/A"). Sub RQVChng() Dim lgrow As Integer lgrow = 7 Do Until Cells(lgrow, 5) = Empty Select Case Cells(lgrow, 17).Value Case 1 Range(Cells(lgrow, 1), Cells(lgrow, 18)).Interior.ColorIndex = xlNone Case 2 Range(Cells(lgrow, 1), Cells(lgrow, 18)).Interior.ColorIndex = 6
View Replies!
View Related
Color Maximum & Minimum Value In Range
I use code as below to find max or min data row, data is in not integer, but match doesn't work, I got error 1004, "Match function's properties can't be accessd". Sub SelectMaxMin(ByVal vSheet As Worksheet) Dim i As Integer, oRange As Range, iRowMax As Integer, iRowMin As Integer For i = 4 To 23 Set oRange = vSheet.Range(Chr(64 + i) & 6 & ":" & Chr(64 + i) & 82) iRowMax = WorksheetFunction.Match(WorksheetFunction.Max(oRange), oRange) + 5 iRowMin = WorksheetFunction.Match(WorksheetFunction.Min(oRange), oRange) + 5 vSheet.Cells(iRowMax, i).Interior.ColorIndex = 40 vSheet.Cells(iRowMin, i).Interior.ColorIndex = 35 Next Set oRange = Nothing End Sub
View Replies!
View Related
Color Cells In 1 Worksheet Based On Range In Another
What i want to do is based on values in a worksheet row to fill the corresponding columns with black color on another worksheet. E.g i have the first row filled with 1,3,5,6,8 then i want the second worksheet on the first row to have filled with black color the 1st,3rd,5th,6th,8th columns and nothing on the others. The same goes for the entire sheet.
View Replies!
View Related
Format Color For Range Of Cells Based On Cell Value
I have been tasked with creating a Macro in order to help speed up a rather simple set of steps that are taken to format a spreadsheet each day. The last step, however, is not so simple. I would like to add a statement to the macro code that does that following: Based on the value of a specific cell, color the cell and all corresponding cells within the same row (from colums A to AA) yellow. Essentially, cells in column D may have a the value "No Allocation", and if they do, I will need to format that cell yellow as well as all the cells within the same row for the colume range A to AA.
View Replies!
View Related
Change Font Color In A Range Based On One Cells Information
I have attached an example file.. Basically I want the text in a range to change to red if one cell="Description". Also, I want a cell's text to turn blue if it's corresponding data in the row says "Click on Title to Follow Link" The example file explains it a lot better.. Thanks for the help.. Sorry if this may seem like a duplicate post but I was not able to get any of the examples I searched for to work that others have posted.. Also, maybe conditional formatting would be an option here but I could not get it to work as it will only meet the first condition that comes across thats true...
View Replies!
View Related
Sub Worksheet Change Range Color Error Due To Protected Sheet
I have this code that works fine until I password the sheet and the range (b9:d65536) is protected. When I protect the sheet and make a change I get a debug error. What would I need to add/change to handle protected cells on a protected sheet? VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ....
View Replies!
View Related
Change Range Format & Color Based On Result Of Drop-Down Choice
I'm using Excel 2007. I have a field with a "YES"/"NO" drop-down box. There are three fields below which have some default formatting (thick purple dot-dot-dash borders). When the drop-down field is "YES", those three fields should change borders to a single thin black bottom border line. OK... so that's the setup. And I should note here that it does in fact format properly. However, the problem I'm having is that the screen does not update / repaint with the new formatting. The thick purple dot-dot-dash borders remain, for the most part. If I page down, then page back up, the new formatting appears just fine. When I switch from "YES" to "NO", again, the formatting changes, but the screen does not update/repaint so the only way to see the new formatting is to again... page up and then page down again. I'm wondering if anyone knows a way to force the screen to repaint/update so that my users will see the new formatting without having to page up/down.
View Replies!
View Related
Conditional Formatting Conflict: Change The Cells Font Color And Bolding As Long As The Value Is Within That Date Range
I have two conditions setup in Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting. The first is setup for alternate row coloring with this formula inside Conditional Formatting: Formula is = MOD(ROW(),2) My Second Condition is Cell Value is between $P$10 and $Q$10. This sets the font bold and a different color. The two cell values are two dates. I want to change the cells font color and bolding as long as the value is within that date range. It works fine, but for cells that are on the row that is colored the second condition doesn't apply for some reason.
View Replies!
View Related
Color Cell Fonts Based On Text Color Of TextBox Controls On UserForm
I have got a userform with lots of controls, One of the action's on a large group of the controls is the same but except for one number here is an example If TextBox107.ForeColor = 255 Then ActiveCell. Offset(0, 53).Font.ColorIndex = 3 If TextBox108.ForeColor = 255 Then ActiveCell.Offset(0, 54).Font.ColorIndex = 3 If TextBox109.ForeColor = 255 Then ActiveCell.Offset(0, 55).Font.ColorIndex = 3 This makes a cell that correlates to the textbox red if the text in the textbox is red. Now, I loads of these textboxes that all need to run the same code with just the Offset value one digit higher than the last and I was hoping I could create a loop to avoid a huge block of code but I can't work out how to make a constant that will +1 with each loop. Also, can I assume that a loop will start with the control with the lowest number i.e. Textbox1 and then work its way through the rest of them in order?
View Replies!
View Related
Color Column Charts Based On Font Color Data Cells
I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1. After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table. I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.
View Replies!
View Related
Macro: Changing Color Of Coloumn Header Based On Rows Color
I wrote a macro to color the cell values in the rows based on their average value. For eg if the cell value is less than 0.2 Avg, they should be red color,if value is between 0.2 and 0.5 it should be yellow. This part is working fine Now based on the color of the rows cells , need to write a macro for the header one. Logic is Coloum header should be in red colour, if in one or more number of rows cells are red. same with yellow ones. Could you please help me out in solving this with logic.
View Replies!
View Related
Change Font Color Based On Adjacent Cell Color
I have two columns. The first one (A) contains cells that have different Fill colors. The second column (B) contains text adjacent to the colored cells. I am trying to change the color of the text in the second column (B) to the corresponding color in the adjacent cell in the first column (A). I don't think conditional formating works well in this situation. I believe the solution would be some sort of macro.
View Replies!
View Related
Link Chart Column Color To Tab Color
I want to be able to link the colors of 8 differnet TABS of individual worksheets that have each been colored differently to the colors of individual columns of a chart - being a summary chart of information in another worksheet that has come from seperate worksheets within the same workbook. So that if someone was to change the color of any tab then the color of the corrosponding color of a column in the chart would change automatically if the color of a TAB is changed.
View Replies!
View Related
Macro To Color Cells Based On Their Font Color
I have a spreadsheet that i download from the net daily, which is seperated into columns of information. I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red. For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc. If the font colour in a1 or a2 ... etc etc is any other colour then do nothing. I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.
View Replies!
View Related
Change Font Color If Cell Color Become Yellow (6)
I have font color white in blank cells in column E and I (from row 5 to row 245) so the visitors will not see the text. If any of these cells become yellow (color code is 6), the font color will become black so visitors can see the text alot far better than white. I've tried this code myself after this post but nothing happen
View Replies!
View Related
Change Text Color Based On Cell Color
I have various row cells in column (F) filled with the color Green. And corresponding text in Column G. How can I change the text of that particular row to white. i.e.: if any cell in column F is Green, change the text color of that row in Column G to white?
View Replies!
View Related
Count By Font Color & Background Color
In excel 2000, I am trying to count the lines in a spreadsheet that has the first cell in each row highlighed in yellow. Additionally, I am trying to count the number of rows that has a red font used in the cell in column D. I have reviewed other posts by searching counting highlighted cells, but I do not understand how to complete the entire process. One of the suggestion was to create a vb script in combination with using =cellindex(??). I do not really understand how to create this, name it and the run it.
View Replies!
View Related
Color Cells Based On Color Of Preceeding Cell
I am looking for a very simple script that will achieve the following: On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order: From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.
View Replies!
View Related
Color Chart Data Points By Cell Color
if it actually possible to colour data points depending on the colour of the cell of the data it refers to? if not is it possible to colour it any other way i have uneven amounts of data for weach month but still want the months to be displayed, this is the best way i can think of of doing this
View Replies!
View Related
Change Color Of Cell Range When Last Cell Receives Data
I need my worksheet to automatically fill in a range of cells (from Range B*:G*) with my yellow color once the last cell in the range (G*) receives data, and automatically switch back to no color if the data in Cell G* is erased. However, if somebody merges a range, it should appear as it would by default - as if no macro exists, if that makes sense. I've attached a sample worksheet.
View Replies!
View Related
Color Panel: Let User Choose Color
There must be a VBA command out there, with which one can call the color panel such that the user can then choose his/her preferred color, I just cannot find it. Could you help me out? With Color-Panel I mean this little interface you get when manually changing the font-color or the fill-color. (it consits out of maybe 8 colors in a row and 5 colors per column and usually has a button "automatic"). I would like to call that interface such that it gives back the color-index of the color choosen by the user.
View Replies!
View Related
Font Color Based On Interior Color
I would like to have cells that conditionally format the font color in the cell based on the background color of the cell. Essentially I have a matrix with some cells highlighted in red, yellow, or orange. There are values in each cell of the matrix. If a value is 0 I want the font to be grey. If the value is NOT 0 AND the cell backgorund color is NOT white, I would like it black and bold. Is there any way to do this in excel or using VB?
View Replies!
View Related
|
TRACKER
