1. B

    Change background color of all named ranges

    HI, I am trying to loop through all the cells in thisworkbook and change the background color to vbyellow. My code so far is as follows: Sub testdata() Dim nm As Variant For Each nm In ThisWorkbook.Names Range(nm.RefersTo).Interior.Color = vbYellow Next nm End Sub It draws an...
  2. L

    VBA Change Entire Columns with specific header to Value instead of Formulas

    Hello, I need help with a bit of VBA code. I have columns AIFinal01-AIFinal20 throughout my sheet. Each column contains a formula going to the bottom. I need to change that formula to a value for all 20 columns. The columns are not in direct order and their are columns between them. Any help is...
  3. D

    making excel recognize it's 20XX not 19XX

    is there a simple way to do this so that it's not PC dependent ? change the settings within the workbook? rather not use formulas
  4. S

    Sumproduct formulas to exclude specific text/values in count and totals

    Hi, Can't attach the the file to the post as there is no option to do so which I need help with but this is where the file is located: What I want to change in this file is to include “IN” and “OFF” on the calendar days that employees are working and...
  5. C

    Conditional SUMPRODUCT to also ignore blanks

    Hi, I have the following code which calculates a weighted average: =SUMPRODUCT(--($U$2:$U$1203=AE3),$AB$2:$AB$1203,$C$2:$C$1203)/SUMIF($U$2:$U$1203,AE3,$C$2:$C$1203) It works very well except that I would like it to ignore blank cells in the range $AB$2:$AB$1203 I tried to change it to the...
  6. S

    MouseMove Event : When mouse is outside the edge or border of command button would like to change the background colour of commandbutton

    Hello How is above statement in RED justified with below code as i am stuck and color does not change when the mouse is outside the border or edge of command button Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)...
  7. L

    Excel convert time to Custom

    Hi No matter how I enter the time in excel, excel will change the format to Custom (h:00:00 am/pm). Why excel does not change the format of the time I entered to "Time" instead of "Custom" ? <tbody> I entered excel convert custom 1:00:00 am 1:00:00 AM h:mm:ss AM/PM 1 am 1:00 AM h:mm...
  8. J

    Adapt some VBA

    hello I have found this code how do I change the code to only change the selected cells to turn white Cells.Interior.ColorIndex = 0 Cells(A12:AE39).Interior.ColorIndex = 0 I only want it to work on A12:AE39 Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count >...
  9. J

    change row colour when cell is selected for ease

    Hello I have a sheet but in struggling to see if I'm on the correct row when im filling it in Can you change the row colour when a cell is selected? ie: if I select any thing between G21:AE21 I can get A21:E21 to change colour But if nothing is selected then don't change anything Hope ive...
  10. V

    Rename part of multiple sheets if it meets my critera

    I already have a script that does most of the mumbo jumbo of what I need, however at the very end of that script, I want to add the part to rename part of some of my sheets to something else I have multiple template sheets and a master sheet within a workbook, those need to remain untouched...
  11. S

    Access Annoyance: ComboBox Ordeal

    Okay, I create a combo box in my form, to work with a query below. It is supposed to search for two columns in the query. It works at first. Then, I change the combo box to sort ascending and then it goes bonkers.. When I click on a name/area (my two fields) it will find the name but a...
  12. D

    conditional formatting 2 colours for a cell.

    Hello, I have a column that is conditionaly formatted. Cells change there fill colour depending upon the value. My cells change to red if the value <-0.1. Then green appears when the value>0.1 I have used rules type "cells that contain". but at the moment empty cells fill green. How can this be...
  13. Z

    VBA to change to a different sheet with a command button

    How can I change sheets based on a userform button. If I have a command button on sheet 1 that when it's clicked a userform pops up. But I also want to automatically change sheets to the current day. So say on sheet 1 there's a command button for Monday, Tuesday, Wednesday, Thursday, Friday...
  14. D

    change default font color

    ok, so i redesigned a template, but what i shoul;d have done is use a new workbook because every time i type something in the template, the font color is red. is there a way to change it so that it's black?? using excel 2010.
  15. F

    VBA: Highlight header row on Autofilter

    I found this thread to set a background color to the column header of the column with an autofilter applied. It works fine on the Worksheet Activate event, but not for the Worksheet Change, Calculate, or Selection Change. Not sure which on I need, but I'm just looking to apply the background...
  16. G

    Not allow users to hide columns in protected sheet

    Hi all, I have a protected sheet, where I want the users to be able to change the size of the columns but not to hide them. In the option "Protect sheet" I can unable "Format columns", but If I do that, the users will not be able to change the size of the columns. Is there a code for this...
  17. D

    Loop through an Array, change values

    Hi there! I want to loop through an array looking for the below values. IF it finds them, I want to change the value, if it doesn't, just move on. <colgroup><col></colgroup><tbody> 10 Very Easy 10 Demonstrated Well 10 Very Satisfied 1 Not Demonstrated 1 Not Satisfied 1 Not Easy...
  18. K

    Vary row number in a VBA formula

    Using Excel 2000</SPAN></SPAN> Hi,</SPAN></SPAN> I have a formula sumproduct running in the VBA =SUMPRODUCT(--($I$8:$L$8=C8:F8)) I want a loop once this formula is filled restart again filling with change of row number =SUMPRODUCT(--($I$9:$L$9=C8:F8)) change as long as data find in the column C...
  19. M

    Change value before closing workbook

    Hi All, i have a workbook, which saves as pdf and xlsx. it works fine & the code is below i'm trying to get it to change cell J4 by 1 before it closes. so it changes from number one to number two and so on before it closes. i have a code that i can get it to work by entering the code in...
  20. D

    PowerPivot Source Change Keep Measures

    Hi, So i have these spreadsheets with database connections and which then get uploaded into a datamodel. Now my question is specifically about the data model, if i change the source to a different table with the exact same layout it works great and i don't need to do any editing all the DAX...

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back